Differences

This shows you the differences between two versions of the page.

--- extract_ddl_with_dbms_metadata.get_ddl [2018/12/08 12:49] – created 0.0.0.0
+++ extract_ddl_with_dbms_metadata.get_ddl [2021/07/30 17:48] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Extract_ddl_with_"dbms_metadata.get_ddl" ======
+===== Different ways of extracting ddl =====
-=====Different ways of extracting ddl=====
+====  Get the semi-colon appended  ====
- set pagesize 0
+<code>
- set linesize 30000
+exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
- set long 500000
+</code>
- set longchunksize 500000
+====  Make the output pretty ====
- set trimspool on
+<code>
- set feed off
+exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
- spool deedeeyell.sql
+</code>
- select 'select dbms_metadata.get_ddl(//INDEX//,**||object_name||**,//&&schema//)||chr(10)||///// from dual;'
+====  Don't recreate storage clause on tables/indexes  ====
- from dba_objects where owner = UPPER('&&schema')
+<code>
- and object_type = 'INDEX'
+BEGIN
- and ...
+dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false)
- /
+dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false );
- spool off
+dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false)
+END;
+/
-===== Get the semi-colon to be appended =====
+</code>
- exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, ‘SQLTERMINATOR’, TRUE );
+====  Return DDL as VARCHAR2 instead of CLOB  ====
-===== Make the pretty output =====
+  * Use get_ddl_text insead of get_ddl
- exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
+====  Get lots of DDL at once  ====
-===== Don't recreate storage clause on tables/indexes =====
+<code>
- exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
+set newpa none pages 0 lines 30000 long 500000 longc 500000 trims on feed off
-===== Return DDL as VARCHAR2 instead of CLOB =====
+select 'select dbms_metadata.get_ddl_text('''||object_type||''','''||object_name||''','''||owner||''') from dual;'
-*Use get_ddl_text insead of get_ddl
+from   dba_objects
-===== Get lots of DDL at once =====
+where  1=1
- select ‘select dbms_metadata.get_ddl(”’|| object_type || ”’,
+and    owner like upper('%&&schema')
- ”’||object_name||”’,”&&schema”) FROM dual;’
+and    object_type like upper('%&&type')
- from   dba_objects where owner = UPPER(‘&&schema’)
+/
- where  1=1
+undef schema type
- and    object_type = ‘INDEX’
+</code>
- select ‘select dbms_metadata.get_ddl(”’ || type || ”’,
+<code>
- ”’ || name || ”’, ”’ || UPPER(‘&&schema’) || ”’) FROM dual;’
+select 'select dbms_metadata.get_ddl(”' || type || ”',
- from   dba_object_size
+”' || name || ”', ”' || UPPER('&&schema') || ”') FROM dual;'
- where  1=1
+from   dba_object_size
- and    owner = UPPER(‘&&schema’)
+where  1=1
- and    type  = ‘TABLE’; — change to each of the valid types
+and    owner = UPPER('&&schema')
+and    type  = 'TABLE'; — change to each of the valid types
+</code>
+====  Get User DDL with related grants (used in clone user as ... or copy user as ...) ====
+<code>
+set long 5000000
+set pages 0
+set lines 2000
+set feedb off
+set verif off
+col deedeeyell for a1000 word_wrap
+exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true );
+exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
-===== Get User DDL with related grants (used in clone user as ... or copy user as ...)=====
+select dbms_metadata.get_ddl('USER', username) deedeeyell
-<code>0@@</code>
+from dba_users
+where  1=1
+and    username = '&&username'
+union all
+select dbms_metadata.get_granted_ddl('ROLE_GRANT', username)
+from dba_users
+where  1=1
+and    username = '&username'
+union all
+select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', username)
+from dba_users
+where  1=1
+and    username = '&username'
+union all
+select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username)
+from dba_users
+where  1=1
+and    username = '&username'
+</code>
-===== Get Table DDL=====
+====  Get Table DDL ====
-<code>1@@</code>
+<code>
+set long 3000000
+set lines 1000
+set pages 0
+set trimspool on
+set feedb off
+col eskewell for a1000
+spool ddl_table.sql
+select dbms_metadata.get_ddl ('TABLE', upper('&&table_name'), upper('&&schema'))||chr(10)||'/' eskewell from dual
+/
+spool off
+</code>
-===== Get View DDL=====
+====  Get View DDL ====
+<code>
  set long 3000000
  set lines 1000
@@ Line 52: / Line 99: @@
  col eskewell for a1000
  spool ddl_view.sql
- select dbms_metadata.get_ddl ('VIEW', '&&view_name', '&&schema')||chr(10)||'/' eskewell from dual;
+ select dbms_metadata.get_ddl ('VIEW', upper('&&view_name'), upper('&&schema'))||chr(10)||'/' eskewell from dual;
  spool off
+</code>
-=====Get Trigger DDL=====
+==== Get Trigger DDL ====
+<code>
  set long 3000000
  set lines 1000
@@ Line 63: / Line 112: @@
  col eskewell for a1000
  spool ddl_trigger.sql
- select dbms_metadata.get_ddl('TRIGGER', '&trigger_name','&schema') ddl
+ select dbms_metadata.get_ddl('TRIGGER', upper('&trigger_name'),upper('&schema')) ddl
  from   dual
  spool off
+</code>
-=====Extract DDL for a table without its integrity contraints=====
+==== Extract DDL for a table without its integrity contraints ====
 If you are having trouble recreating table data because of integrity contraints, trying importing the data first, then the constraints:
+<code>
  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', FALSE);
-then extract  the ddl using one of the methods above.<br />
+</code>
+then extract  the ddl using one of the methods above.\\
 Finally, extract the constraint ddl and run it separately:
+<code>
  dbms_metadata.get_ddl('REF_CONSTRAINT','&&contraint_name');
+</code>
-=====Generate DDL and store it in a table=====
+==== Generate DDL and store it in a table ====
-from: [[http://www.myoracleguide.com/s/gen_schema.htm]]<br />
+from: [[http://www.myoracleguide.com/s/gen_schema.htm]]\\
-<code>2@@</code>
+<code>
+ create table ddl_scripts(
+ object_name varchar2(50),
+ object_type varchar2(30),
+ text varchar2(4000));
-=====Extract db_links with password=====
+ --  Format the DDL text using trigger
+ create or replace trigger ddl_scripts_format_trg before insert on ddl_scripts
+ for each row
+ declare
+ n1 number;
+ n2 number;
+ BEGIN
+ n1 := instr(:new.text, '"."',1,1);
+ n2 := instr(:new.text, '"', 1,1);
+ if n1<50 then
+>new.text := replace(substr(:new.text,1,n2-1)||substr(:new.text,n1+3), '"', null);
+>new.text := ltrim(rtrim(:new.text, chr(10)), chr(10));
+>new.text := ltrim(rtrim(:new.text, chr(32)), chr(32));
+>new.text := ltrim(rtrim(:new.text, chr(10)), chr(10));
+>new.text := ltrim(rtrim(:new.text, chr(32)), chr(32));
+ end if;
+ END;
+ /
+ ---- Run the following PL/SQL code to insert DDL scripts into the above table
+ ---- Comment the object types that are not required
+ ---- Additional filter can be added for the cursor queries
+ declare
+ a1 varchar2(32767);
+ str varchar2(32767);
+ n1 number;
+ cur_user varchar2(30);
+ begin
+ select '"'||upper(username)||'"."' into cur_user from user_users;
+ for c1 in(select a.table_name from user_tables a where a.table_name
+ not in(select object_name from user_recyclebin) order by 1)
+ LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TABLE', c1.table_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.table_name, 'TABLE', a1);
+     for c2 in(select index_name from user_indexes where table_name=c1.table_name and index_type in('NORMAL', 'BITMAP') order by 1)
+     LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('INDEX', c2.index_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.table_name, 'INDEX', a1);
+     END LOOP;
+ END LOOP;
+ -- Trigger scripts
+ for c1 in(select table_name, trigger_name from user_triggers where trigger_name
+ not in(select object_name from user_recyclebin) order by 1,2) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TRIGGER', c1.trigger_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.table_name, 'TRIGGER', a1);
+ END LOOP;
+ -- Views
+ for c1 IN(select view_name from user_views order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('VIEW', c1.view_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.view_name, 'VIEW', a1);
+ END LOOP;
+ -- Sequence Scripts
+ for c1 IN(select sequence_name from user_sequences order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('SEQUENCE', c1.sequence_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.sequence_name, 'SEQUENCE', a1);
+ END LOOP;
+ -- Directories
+ for c1 in(select directory_name from all_directories order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('DIRECTORY', c1.directory_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.directory_name, 'DIRECTORY', a1);
+ END LOOP;
+ -- Materialized Views
+ for c1 in(select mview_name from user_mviews order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('MATERIALIZED_VIEW', c1.mview_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.mview_name, 'MATERIALIZED VIEW', a1);
+ END LOOP;
+ -- Materialized View Logs
+ for c1 in(select log_table from user_mview_logs order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG', c1.log_table), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.log_table, 'MATERIALIZED_VIEW_LOG', a1);
+ END LOOP;
+ -- Synonyms
+ for c1 in(select synonym_name from user_synonyms order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('SYNONYM', c1.synonym_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.synonym_name, 'SYNONYM', a1);
+ END LOOP;
+ --  Types
+ for c1 in(select type_name from user_types order by 1) LOOP
+         select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TYPE', c1.type_name), 32767,1) INTO a1 from dual;
+         insert into ddl_scripts values(c1.type_name, 'TYPE', a1);
+ END LOOP;
+ END;
+ /
+</code>
+==== Extract db_links with password ====
 Database links extracted with get_ddl (or a select from dba_db_links) do not show the password so we need a script that goes into the base tables
-<code>3@@</code>
+<code>
-<code>4@@</code>
+select dbms_metadata.get_ddl('DB_LINK',db.db_link,db.owner)
+from   dba_db_links db
+/
+</code>
+<code>
+set headi off lines 1000 pages 0 long 1000
+select 'From user '||decode (u.name, 'PUBLIC', 'SYS', u.name) || ':' ||chr(10)||
+       'create ' || decode (u.name, 'PUBLIC', 'public ') || 'database link ' || '"' || l.name || '"' ||
+       ' connect to ' || '"' || l.userid || '"' ||
+       ' identified by values ' || '"' || l.passwordx || '"' ||
+       ' using ' || '"' || l.host || '"' || ';' ||chr(10) link_ddl
+from   sys.link$ l
+,      sys.user$ u
+where  l.owner# = u.user#
+order  by u.name
+/
+</code>
-=====Generate script for creating schema objects=====
+==== Generate script for creating schema objects ====
-<code>5@@</code>
+<code>
+ begin
+ dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );
+ dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
+ dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', true );
+ dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
+ dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', true );
+ end;
+ /
+ set long 1000000
+ set lines 255
+ set longchunksize 255
+ set pages 0
+ set trimspool ON
+ col str for a255 word_wrapped
+ select dbms_metadata.get_ddl(
+        decode ( object_type
+               , 'JAVA SOURCE', 'JAVA_SOURCE'
+               , 'MATERIALIZED VIEW', 'MATERIALIZED_VIEW'
+               , 'MATERIALIZED VIEW LOG', 'MATERIALIZED_VIEW_LOG'
+               , 'PACKAGE', 'PACKAGE_SPEC'
+               , 'PACKAGE BODY', 'PACKAGE_BODY'
+               , object_type
+               )
+ ,      object_name
+ ,      owner) str
+ from   dba_objects
+ where  1=1
+ and    owner='&OWNER'
+ order  by case when object_type IN('SEQUENCE','TABLE') then 0 else 1 end;
+--  Create constraints
+select dbms_metadata.get_ddl(
+decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner) str
+FROM dba_constraints
+WHERE owner=//
+and constraint_type IN('P', 'R', 'U')
+ORDER BY case when constraint_type IN('P','U') then 0 else 1 end;
+ -- Query the table ddl_scripts
+ SQL> set pagesize 0
+ SQL> set linesize 32767
+ SQL> set trimspool ON
+ SQL spool c:\\temp\\ddl.txt
+ SQL> select text from ddl_scripts order by
+      case when object_type in('TABLE', 'INDEX', 'TRIGGER') then 0 else 1 end,
+      object_name, decode(object_type, 'TABLE',1,'TRIGGER',3,'INDEX',2);
+ SQL> spool off
+</code>
 All possible extractable keywords can be found here:[[http://psoug.org/reference/dbms_metadata.html]]