Re: Simple Export Question

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 Thu Jun 01 2000 - 00:00:00 CDT