Re: Copying tables form ONE Schema to Another SChema in same instance

From: HARI OM <hari_om_at_hotmail.com>
Date: 6 Oct 2004 09:00:50 -0700
Message-ID: <d1d5ebe4.0410060800.4c38f959@posting.google.com>

Thanks Daniel and Sybrabd.

Here is what I did to move DIN tables to MUK Schemas: STEP 1 ./imp system/manager fromuser=din touser=muk file=exp_din.dmp rows=n indexes=n indexfile=create_muk_table.sql

STEP 2: Edited the above SQL File and replaced DIN Table Space with MUK Table Spaces and reomved all REM Statements

STEP 3:
./imp system/manager fromuser=din touser=muk file=exp_din.dmp rows=n indexfile=create_muk_index.sql

STEP 4: Edited the above SQL File and replaced DIN Table Space with MUK Table Spaces and reomved all CONNECT Statements

STEP 5: Executed CREATE_MUK_TABLE.SQL
It successfully create TABLE Objects in this Tablespace.

STEP 6: Now I Imported all the DATA using IMP to MUK Tables. ./imp system/manager fromuser=din touser=muk file=exp_din.dmp log=muk.log indexes=n ignore=y

At this point it is THROWING following ALL ERRORS (as reorded in MUK.LOG file)

---

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing DIN's objects into MUK

. . importing table                          "EMP"

IMP-00019: row rejected due to ORACLE error 2291

IMP-00003: ORACLE error 2291 encountered ORA-02291: integrity constraint (MUK.FK_EMP_CLASS) violated - parent key not found
Column 1 714
Column 2 203
Column 3 observ
Column 4 6730f12a-9ad2-11d8-aa68-e0f155622ad3 Column 5 A

IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (MUK.FK_EMP_CLASS) violated - parent

key not found
Column 1 715
Column 2 203
Column 3 observ
Column 4 6730f12b-9ad2-11d8-aa68-e0f155622ad3 Column 5 A

---

At this time I followed all the above steps with adding "constraints=n"
./imp system/manager fromuser=din touser=muk file=exp_din.dmp log=muk.log indexes=n ignore=y constraint=n

BUT STILL I GET BUNCH of all above errors.... can anyone help me out on this?

THANKS! HARI OM Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1096937167.739094_at_yasure>...
> HARI OM wrote:
>
> > SUBJECT: Copying tables form ONE Schema to Another SChema in same
> > instance
> >
> > I am using Oracle 9.2.0.2 on IBM AIX 5.1L System.
> > I have a instance names "KHAR" and have 2 SCHEMAS in it namely: "DIN"
> > and "MUK".
> >
> > Schema DIN is in Tablespace DIN and
> > Schema MUK is in Tablespace MUK.
> >
> > I have around 200 populated tables in "Schema DIN" and I took FULL
> > Export of this User "DIN" - "din.dmp" last week.
> >
> > Now, I would like to replicate or copy these DIN Schema Tables to MUK
> > Schema's. How can I do that.
> >
> > I tried doing IMPORT:
> > ./imp system/***@khar fromuser=DIN touser=MUK
> > and enter the export file as "din.dmp"
> >
> > It did create these tables under Schema MUK. However, it created it
> > under DIN Tablespace and not under MUK Tablespace.....
> >
> > what is the better way of doing this? what am I doing wrong?
> >
> > THANKS!
> >
> > HARI OM
>
> ALTER TABLE MOVE ....
Received on Wed Oct 06 2004 - 11:00:50 CDT