Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_METADATA to get dependent DDL
DBMS_METADATA to get dependent DDL [message #289087] Wed, 19 December 2007 09:57 Go to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Hi,

I am trying to export certain objects from one schema to another using DBMS_METADATA. How can I get the package to generate dependent SQL in the correct order? For example, if there is a foreign key constraint from table XX to table YY, then table YY should be created first.

Also, when generating table DDL using DBMS_METADATA how do I suppress partitioning information. I have tried setting STORAGE and SEGMENT_ATTRIBUTES to FALSE but the generated DDL still has partitions/sub-partitions.

Thanks in advance.

Rajesh
Re: DBMS_METADATA to get dependent DDL [message #289100 is a reply to message #289087] Wed, 19 December 2007 13:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
rs1969 wrote on Wed, 19 December 2007 07:57


I am trying to export certain objects from one schema to another using DBMS_METADATA. How can I get the package to generate dependent SQL in the correct order? For example, if there is a foreign key constraint from table XX to table YY, then table YY should be created first.




SELECT DBMS_METADATA.GET_DDL ('TABLE', ut.table_name)
FROM   user_tables ut, user_constraints uc
WHERE  ut.table_name = uc.table_name (+)
START WITH  ut.table_name NOT IN 
       (SELECT table_name 
        FROM   user_constraints
        WHERE  r_constraint_name IS NOT NULL)
CONNECT BY PRIOR uc.constraint_name = uc.r_constraint_name
/ 


[Updated on: Wed, 19 December 2007 13:13]

Report message to a moderator

Re: DBMS_METADATA to get dependent DDL [message #289106 is a reply to message #289100] Wed, 19 December 2007 13:19 Go to previous messageGo to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Thanks Barbara, will try that.
Re: DBMS_METADATA to get dependent DDL [message #289113 is a reply to message #289087] Wed, 19 December 2007 13:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
rs1969 wrote on Wed, 19 December 2007 07:57


Also, when generating table DDL using DBMS_METADATA how do I suppress partitioning information. I have tried setting STORAGE and SEGMENT_ATTRIBUTES to FALSE but the generated DDL still has partitions/sub-partitions.




It seems like there ought to be some sort of set_transform_param, but I can't find one. Unless somebody else can find it, you can always parse it using substr and instr as demonstrated below.

SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (test_col NUMBER,
  3    CONSTRAINT test_col_fk FOREIGN KEY (test_col) REFERENCES dept (deptno))
  4    PARTITION BY RANGE (test_col)
  5  	 (PARTITION p_1 VALUES LESS THAN (10),
  6  	  PARTITION p_2 VALUES LESS THAN (20))
  7  /

Table created.

SCOTT@orcl_11g> -- with partitions:
SCOTT@orcl_11g> SELECT DBMS_METADATA.GET_DDL ('TABLE', 'TEST_TAB') FROM DUAL
  2  /

  CREATE TABLE "SCOTT"."TEST_TAB"
   (	"TEST_COL" NUMBER,
	 CONSTRAINT "TEST_COL_FK" FOREIGN KEY ("TEST_COL")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("TEST_COL")
 (PARTITION "P_1"  VALUES LESS THAN (10)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS ,
 PARTITION "P_2"  VALUES LESS THAN (20)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS )



SCOTT@orcl_11g> -- without partitions:
SCOTT@orcl_11g> SELECT SUBSTR
  2  	      (DBMS_METADATA.GET_DDL ('TABLE', 'TEST_TAB'), 1,
  3  	       INSTR (DBMS_METADATA.GET_DDL ('TABLE', 'TEST_TAB'), 'PARTITION') - 1)
  4  FROM   DUAL
  5  /

  CREATE TABLE "SCOTT"."TEST_TAB"
   (	"TEST_COL" NUMBER,
	 CONSTRAINT "TEST_COL_FK" FOREIGN KEY ("TEST_COL")
	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



SCOTT@orcl_11g> DROP TABLE test_tab
  2  /

Table dropped.

SCOTT@orcl_11g> 

Re: DBMS_METADATA to get dependent DDL [message #289209 is a reply to message #289100] Thu, 20 December 2007 04:18 Go to previous messageGo to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Barbara Boehmer wrote on Wed, 19 December 2007 13:12



SELECT DBMS_METADATA.GET_DDL ('TABLE', ut.table_name)
FROM   user_tables ut, user_constraints uc
WHERE  ut.table_name = uc.table_name (+)
START WITH  ut.table_name NOT IN 
       (SELECT table_name 
        FROM   user_constraints
        WHERE  r_constraint_name IS NOT NULL)
CONNECT BY PRIOR uc.constraint_name = uc.r_constraint_name
/ 





This is giving duplicate rows as there can be more than one constraint for a table.

Re: DBMS_METADATA to get dependent DDL [message #289210 is a reply to message #289209] Thu, 20 December 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If so fix it and post here the correction.

Regards
Michel
Re: DBMS_METADATA to get dependent DDL [message #289212 is a reply to message #289209] Thu, 20 December 2007 04:28 Go to previous messageGo to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Ok I have changed it to

SELECT DBMS_METADATA.GET_DDL ('TABLE', x.table_name)
FROM   (
       SELECT DISTINCT ut.table_name
       FROM   user_tables ut, user_constraints uc
       WHERE  ut.table_name = uc.table_name (+)
       START WITH  ut.table_name NOT IN 
             (SELECT table_name 
              FROM   user_constraints
              WHERE  r_constraint_name IS NOT NULL)
       CONNECT BY PRIOR uc.constraint_name = uc.r_constraint_name) x
/
Re: DBMS_METADATA to get dependent DDL [message #289516 is a reply to message #289212] Sun, 23 December 2007 04:51 Go to previous messageGo to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
Unfortunately, adding the DISTINCT clause to the SELECT returns results in sorted order.

Is there any other way to get unique table names but in the order of foreign key dependency?

Any help is appreciated.
Re: DBMS_METADATA to get dependent DDL [message #289527 is a reply to message #289516] Sun, 23 December 2007 11:31 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I always thought that ORDER BY (and not DISTINCT) ensures that records are returned in desired order.
Re: DBMS_METADATA to get dependent DDL [message #289530 is a reply to message #289516] Sun, 23 December 2007 11:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
COLUMN lvl NOPRINT
SELECT DBMS_METADATA.GET_DDL ('TABLE', ut.table_name), MIN (LEVEL) lvl
FROM   user_tables ut, user_constraints uc
WHERE  ut.table_name = uc.table_name (+)
START WITH  ut.table_name NOT IN 
       (SELECT table_name 
        FROM   user_constraints
        WHERE  r_constraint_name IS NOT NULL)
CONNECT BY PRIOR uc.constraint_name = uc.r_constraint_name
GROUP  BY ut.table_name 
ORDER BY lvl;


Re: DBMS_METADATA to get dependent DDL [message #292576 is a reply to message #289087] Wed, 09 January 2008 04:25 Go to previous messageGo to next message
rs1969
Messages: 21
Registered: December 2007
Location: UK
Junior Member
The attached script uses DBMS_METADATA to extract DDL from one schema and run it on another schema. The DDL statement can be more than 32K as it uses a CLOB variable. You can use this script as a reference and expand it for specific needs.

Thanks a lot to Barbara for giving me a headstart.
Re: DBMS_METADATA to get dependent DDL [message #292585 is a reply to message #292576] Wed, 09 January 2008 04:50 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for sharing.
Now you have to improve it to include other object types. Wink

Regards
Michel
Previous Topic: Merge Problem
Next Topic: Is it possible to know the code of any oracle functions
Goto Forum:
  


Current Time: Wed Dec 07 18:34:13 CST 2016

Total time taken to generate the page: 0.07811 seconds