Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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? Received on Sat Jun 03 2000 - 00:00:00 CDT