RE: Using dbms_metadata to extract a full schema
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 4 May 2004 15:07:19 -0700
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
-----------------------------------------------------------------
Other related posts:
