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

Revision as of 20:36, 6 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 ...
/

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

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

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