Happy new year 🙂
Today I had to import a subset of a database and the challenge was to restore a parent table without restoring its children. It took me some minutes to write the code, but it would have taken days to restore the whole database.
CREATE TABLE t1( c1 NUMBER CONSTRAINT t1_pk PRIMARY KEY); INSERT INTO t1 (c1) VALUES (1); CREATE TABLE t2( c1 NUMBER CONSTRAINT t2_t1_fk REFERENCES t1, c2 NUMBER CONSTRAINT t2_pk PRIMARY KEY); INSERT INTO t2 (c1, c2) VALUES (1, 2); CREATE TABLE t3( c2 NUMBER CONSTRAINT t3_t2_fk REFERENCES t2, c3 NUMBER CONSTRAINT t3_pk PRIMARY KEY); INSERT INTO t3 (c2, c3) VALUES (2, 3); CREATE TABLE t4( c3 NUMBER CONSTRAINT t4_t3_fk REFERENCES t3, c4 NUMBER CONSTRAINT t4_pk PRIMARY KEY); INSERT INTO t4 (c3, c4) VALUES (3, 4); COMMIT; expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scott.dmp reuse_dumpfiles=y
Now what happen if I want to restore T2 and T3 ?
If possible, I check the dictionary for foreign keys from other tables pointing to T2 and T3.
SELECT constraint_name
FROM user_constraints
WHERE (r_constraint_name) IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name IN ('T2', 'T3'))
AND table_name NOT IN ('T2', 'T3');
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
T4 T4_T3_FK
T4 points to T3 and T4 has data.
Now I can drop my tables with the cascade options
drop table t2 cascade constraints; drop table t3 cascade constraints;
Now I import, first the tables, then the referential constraints dropped with the cascade clause and not on T2/T3.
impdp scott/tiger tables=T2,T3 directory=DATA_PUMP_DIR dumpfile=scott.dmp impdp scott/tiger "include=ref_constraint:\='T4_T3_FK'" directory=DATA_PUMP_DIR dumpfile=scott.dmp
It’s probably possible to do it in one import, but the include syntax is horrible. I tried there