Thanks for the question, Michael.
Asked: August 03, 2010 - 6:36 pm UTC
Last updated: August 05, 2010 - 7:41 am UTC
Version: Oracle 10.2.0
Viewed 1000+ times
You Asked
I reverse-engineered DDL CREATE TABLE scripts for entire schema to apply them against another (empty) schema. Scripts were generated correctly (code below), but because the foreign key is part of generated CREATE TABLE DDL I cannot apply scripts in the order how CREATE TABLE were generated in the file.
Looks like I need to create first the tables which are not having foreign keys. How to force GET_DDL generate CREATE TABLE statements in right order?
Or may be exists the better way?
Example below:
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
spool DDL-table.txt
SELECT DBMS_METADATA.GET_DDL(object_type,object_name) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'AND STATUS='VALID';
spool off
CREATE TABLE ZZZ.A (
I NUMBER,
J NUMBER,
PRIMARY KEY I,
FOREIGN KEY J REFERENCES ZZZ.B (J) ON DELETE CASCADE ENABLE);
CREATE TABLE ZZZ.B ( J NUMBER, PRIMARY KEY J);
Thanks!
Michael.
and Tom said...
Is this answer out of date? If it is, please let us know via a Comment