Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Simple Export Question

Re: Simple Export Question

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/06/02
Message-ID: <3937F94E.A409F28B@0800-einwahl.de>#1/1

Try to create the export with consistent=yes. Perhaps it has the side effect to consider also the sequence of foreign key relationships.

Martin

Mahesh Swaminathan wrote:
>
> Here is my problem which i can express with this example:
> Assume that i have a user and 2 tables defined as follows and then an
> export:
>
> CREATE USER TEST1 IDENTIFIED BY TEST1;
> GRANT CONNECT, RESOURCE TO TEST1;
> CREATE TABLE TEST1.B
> (
> NUM INTEGER NOT NULL,
> STR VARCHAR2(50) NOT NULL,
> PRIMARY KEY(NUM)
> );
>
> INSERT INTO TEST1.B(NUM,STR) VALUES(1,'HELLO');
> INSERT INTO TEST1.B(NUM,STR) VALUES(2,'HELLO');
> INSERT INTO TEST1.B(NUM,STR) VALUES(3,'HELLO');
> INSERT INTO TEST1.B(NUM,STR) VALUES(4,'HELLO');
> INSERT INTO TEST1.B(NUM,STR) VALUES(5,'HELLO');
>
> CREATE TABLE TEST1.A
> (
> REFNUM INTEGER NOT NULL
> CONSTRAINT fk_T1_REFNUM REFERENCES TEST1.B(NUM),
> STR2 VARCHAR2(50) NOT NULL,
> PRIMARY KEY(REFNUM)
> );
>
> INSERT INTO TEST1.A(REFNUM,STR2) VALUES(1,'HELLO');
> INSERT INTO TEST1.A(REFNUM,STR2) VALUES(2,'HELLO');
> INSERT INTO TEST1.A(REFNUM,STR2) VALUES(3,'HELLO');
> INSERT INTO TEST1.A(REFNUM,STR2) VALUES(4,'HELLO');
> INSERT INTO TEST1.A(REFNUM,STR2) VALUES(5,'HELLO');
>
> EXP SYSTEM/manager owner=TEST1 ROWS=yes file=test1.dmp consistent=yes
>
> and a second user defined like this :
>
> CREATE USER TEST2 IDENTIFIED BY TEST2;
>
> GRANT CONNECT, RESOURCE TO TEST2;
>
> CREATE TABLE TEST2.B
> (
> NUM INTEGER NOT NULL,
> STR VARCHAR2(50) NOT NULL,
> PRIMARY KEY(NUM)
> );
>
> CREATE TABLE TEST2.A
> (
> REFNUM INTEGER NOT NULL
> CONSTRAINT fk_T2_REFNUM REFERENCES TEST2.B(NUM),
> STR2 VARCHAR2(50) NOT NULL,
> PRIMARY KEY(REFNUM)
> );
>
> Now with the FK constraint defined as in TEST2.A the following import fails.
> IMP system/manager FROMUSER=TEST1 TOUSER=TEST2 ignore=yes constraints=no
> grants=no file=test1.dmp
>
> So how do i make sure that the import or export sorts out this order and
> imports/exports the tables in the correct order? (in this case, B first and
> the A) Or is the only way to disable constraints and then do an import and
> then enable constarints again?
>
> Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid> wrote in message
> news:0f6882c8.7b3e870c_at_usw-ex0102-014.remarq.com...
> > When you export constraints [PK, UK, FK] are exported with the
> > table unless you specifiy constraints=n on the export.
> >
> > When you import Oracle will attempt to re-establish the
> > constraints. If it is unable to do so the imp will issue error
> > messages. For FK that reference tables that do not exist in the
> > new instance Oracle will not automatically try to create the
> > referenced table. It will have to be part of the export set or
> > the constraint will not be re-established.
> >
> > If you are exporting related tables with database defined
> > referential integrity constraints on them you may want to use
> > the consistent=y parameter on the export.
> >
> >
> > * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network
 *
> > The fastest and easiest way to search and participate in Usenet - Free!
> >
> >
Received on Fri Jun 02 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US