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

Revision as of 21:32, 11 November 2012

Different ways of extracting ddl

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

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

Make the pretty output

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

Don't recreate storage clause on tables/indexes

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

Return DDL as VARCHAR2 instead of CLOB

• Use get_ddl_text insead of get_ddl

Get lots of DDL at once

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

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 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', '&&view_name', '&&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', '&trigger_name','&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: [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;

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

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