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

Revision as of 14:51, 12 December 2011

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

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

select dbms_metadata.get_ddl('USER', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', username) || '/' ddl
from dba_users
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username) || '/' ddl
from dba_users;

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

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','<REF_CONS_NAME>');