RE: Using dbms_metadata to extract a full schema

From: <Jared.Still_at_radisys.com>
Date: Tue, 4 May 2004 15:07:19 -0700
Message-ID: <OF6F03B7C0.5DC2CB70-ON88256E8A.0077F9FD-88256E8A.00782CF5@radisys.com>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 04 2004 - 16:52:23 CDT