Difference between revisions of "Extract ddl with "dbms metadata.get ddl""

Latest revision as of 12:42, 3 November 2018

Different ways of extracting ddl[edit]

set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
spool deedeeyell.sql
select 'select dbms_metadata.get_ddl(INDEX,||object_name||,&&schema)||chr(10)||/ from dual;'
from dba_objects where owner = UPPER('&&schema')
and object_type = 'INDEX'
and ...
/
spool off

Get the semi-colon to be appended[edit]

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, ‘SQLTERMINATOR’, TRUE );

Make the pretty output[edit]

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );

Don't recreate storage clause on tables/indexes[edit]

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );

Return DDL as VARCHAR2 instead of CLOB[edit]

• Use get_ddl_text insead of get_ddl

Get lots of DDL at once[edit]

select ‘select dbms_metadata.get_ddl(”’|| object_type || ”’,
”’||object_name||”’,”&&schema”) FROM dual;’
from   dba_objects where owner = UPPER(‘&&schema’)
where  1=1
and    object_type = ‘INDEX’

select ‘select dbms_metadata.get_ddl(”’ || type || ”’,
”’ || name || ”’, ”’ || UPPER(‘&&schema’) || ”’) FROM dual;’
from   dba_object_size
where  1=1
and    owner = UPPER(‘&&schema’)
and    type  = ‘TABLE’; — change to each of the valid types

Get User DDL with related grants (used in clone user as ... or copy user as ...)[edit]

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 );

select dbms_metadata.get_ddl('USER', username) deedeeyell
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'

Get Table DDL[edit]

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', '&&table_name', '&&schema')||chr(10)||'/' eskewell from dual
/
spool off

Get View DDL[edit]

set long 3000000
set lines 1000
set pages 0
set trimspool on
set feedb off
col eskewell for a1000
spool ddl_view.sql
select dbms_metadata.get_ddl ('VIEW', '&&view_name', '&&schema')||chr(10)||'/' eskewell from dual;
spool off

Get Trigger DDL[edit]

set long 3000000
set lines 1000
set pages 0
set trimspool on
set feedb off
col eskewell for a1000
spool ddl_trigger.sql
select dbms_metadata.get_ddl('TRIGGER', '&trigger_name','&schema') ddl
from   dual
spool off

Extract DDL for a table without its integrity contraints[edit]

If you are having trouble recreating table data because of integrity contraints, trying importing the data first, then the constraints:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', FALSE);

then extract the ddl using one of the methods above.
Finally, extract the constraint ddl and run it separately:

dbms_metadata.get_ddl('REF_CONSTRAINT','&&contraint_name');

Generate DDL and store it in a table[edit]

from: [1]

 create table ddl_scripts(
 object_name varchar2(50),
 object_type varchar2(30),
 text varchar2(4000));


 --  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;
 /

Extract db_links with password[edit]

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

select dbms_metadata.get_ddl('DB_LINK',db.db_link,db.owner)
from   dba_db_links db
/

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
/

Generate script for creating schema objects[edit]

 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=''
 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

All possible extractable keywords can be found here:[2]