Howto import an oracle dump in an different tablespace -

-

i want import oracle dump different tablespace.

i have tablespace used user a. i've revoked dba on user , given him grants connect , resource. i've dumped command

exp a/*** owner=a file=oracledump.dmp log=log.log compress=y

now want import dump tablespace b used user b. i've given him grants on connect , resource (no dba). i've executed following import:

imp b/*** file=oracledump.dmp log=import.log fromuser=a touser=b

the result log lots of errors:

imp-00017: following statement failed oracle error 20001: "begin dbms_stats.set_table_stats imp-00003: oracle error 20001 encountered ora-20001: invalid or inconsistent input values

after i've tried same import command option statistics=none. resulted in following errors:

ora-00959: tablespace 'a_tblspace' not exist

how should done?

note: lot of columns of type clob. looks problems have that.

note2: oracle versions mixture of 9.2, 10.1 , 10.1 xe. don't think has versions.

you've got couple of issues here.

firstly, different versions of oracle you're using reason table statistics error - had same issue when of our oracle 10g databases got upgraded release 2, , still on release 1 , swapping .dmp files between them.

the solution worked me use same version of exp , imp tools exporting , importing on different database instances. easiest using same pc (or oracle server) issue of exporting , importing commands.

secondly, suspect you're getting ora-00959: tablespace 'a_tblspace' not exist because you're trying import .dmp file full-blown oracle database 10g express edition (xe) database, which, default, creates single, predefined tablespace called users you.

if that's case, you'll need following..

  1. with .dmp file, create sql file containing structure (tables):

    imp <xe_username>/<password>@xe file=<filename.dmp> indexfile=index.sql full=y

  2. open indexfile (index.sql) in text editor can find , replace on entire file, , issue following find , replace statements in order (ignore single quotes.. '):

    find: 'rem<space>' replace: <nothing>

    find: '"<source_tablespace>"' replace: '"users"'

    find: '...' replace: 'rem ...'

    find: 'connect' replace: 'rem connect'

  3. save indexfile, run against oracle express edition account (i find it's best create new, blank xe user account - or drop , recreate if i'm refreshing):

    sqlplus <xe_username>/<password>@xe @index.sql

  4. finally run same .dmp file created indexfile against same account import data, stored procedures, views etc:

    imp <xe_username>/<password>@xe file=<filename.dmp> fromuser=<original_username> touser=<xe_username> ignore=y

you may pages of oracle errors when trying create objects such database jobs oracle try use same database identifier, fail you're on different database.


Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more