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

Revision as of 13:49, 8 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

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

Get the semi-colon to be appended:

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

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

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

set long 3000000
set lines 1000
set pages 0
set feedb off
col eskewell for a1000
spool deedeeyell
select DBMS_METADATA.GET_DDL ('VIEW', 'V_CMDB_MESSAGEFLOW', 'SAPBW')||chr(10)||'/' eskewell 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>');