With slight modifications, this can become a script that will dump an entire schema into separate files that can then be run to recreate the schema. Thanks Srinivasan. Jared ------- -- dump_ddl.sql -- requires Oracle 9i+ set long 10000 set heading off set pages 10000 set feedback off set verify off begin dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE); dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE); end; / set trimspool on col ownernm noprint new_value ownernm prompt Schema Owner: set echo off feed off term off select upper('&1') ownernm from dual; col sqlfile noprint new_value sqlfile select lower('&ownernm') || '_ddl_gen.sql' sqlfile from dual / set term on feed on spool &&sqlfile declare type object_types is table of varchar2(30) index by binary_integer; objects_tbl object_types; v_owner varchar2(30) := upper('&&ownernm'); begin objects_tbl(1) := 'TABLE'; objects_tbl(2) := 'INDEX'; objects_tbl(3) := 'CONSTRAINT'; objects_tbl(4) := 'VIEW'; objects_tbl(5) := 'FUNCTION'; objects_tbl(6) := 'PROCEDURE'; objects_tbl(7) := 'PACKAGE_SPEC'; objects_tbl(8) := 'PACKAGE_BODY'; objects_tbl(9) := 'TRIGGER'; for i in objects_tbl.first .. objects_tbl.last loop dbms_output.put_line('spool ' || lower(v_owner) || '_' || to_char(i) || '_' || lower(objects_tbl(i)) || '.sql'); dbms_output.put_line('select dbms_metadata.get_ddl(object_type,object_name,owner)'); dbms_output.put_line('from dba_objects'); dbms_output.put_line('where object_type = ' || '''' || objects_tbl(i) || ''''); dbms_output.put_line('and owner = ' || '''' || v_owner || '''' || ';'); end loop; dbms_output.put_line('spool off'); end; / spool off @&&sqlfile set feedback on set verify on set heading on set pages 1000 undefine ownernm ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------