This is an old revision of the document!
Table of Contents
Extract_ddl_with_"dbms_metadata.get_ddl"
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 (used in clone user as ... or copy user as ...)
0@@
Get Table DDL
1@@
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.<br /> 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: http://www.myoracleguide.com/s/gen_schema.htm<br />
2@@
Extract db_links with password
Database links extracted with get_ddl (or a select from dba_db_links) do not show the password so we need a script that goes into the base tables
3@@
4@@
Generate script for creating schema objects
5@@
All possible extractable keywords can be found here:http://psoug.org/reference/dbms_metadata.html