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

Revision as of 20:32, 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 );