extract_ddl_with_dbms_metadata.get

Different ways of extracting ddl

Different ways of extracting ddl

Get the semi-colon appended

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

Make the output pretty

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

Don't recreate storage clause on tables/indexes

BEGIN
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false)
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false );
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false)
END;
/

Return DDL as VARCHAR2 instead of CLOB

Use get_ddl_text insead of get_ddl

Get lots of DDL at once

set newpa none pages 0 lines 30000 long 500000 longc 500000 trims on feed off
select 'select dbms_metadata.get_ddl_text('''||object_type||''','''||object_name||''','''||owner||''') from dual;'
from   dba_objects
where  1=1
and    owner like upper('%&&schema')
and    object_type like upper('%&&type')
/
undef schema type

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

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

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

Get View DDL

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

Get Trigger DDL

 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', upper('&trigger_name'),upper('&schema')) ddl
 from   dual
 spool off

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:

 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

from: http://www.myoracleguide.com/s/gen_schema.htm

 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

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

 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

All possible extractable keywords can be found here:http://psoug.org/reference/dbms_metadata.html

Table of Contents