| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using dbms_metadata to extract a full schema
Daniel,
Try this. It works for me. I got the code from some web-site (SearchOracle.com, I think). You may have to filter out (in the WHERE condition) certain object types like LOB which cause this package to fail. However procedures, functions, etc., are extracted.
set long 10000
set heading off
set pages 10000
set feedback off
set verify off
Define ownernm='&owner'
Define objtype='&object_type'
accept pwd prompt "Enter Password for user &ownernm : " hide
connect &ownernm/&pwd
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',T
RUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMIN
ATOR',TRUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_A
TTRIBUTES',TRUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',
TRUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPAC
E',TRUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICA
TION',TRUE);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRU
E);
exec
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAIN
TS',TRUE);
set trimspool on
spool &ownernm-ddl
select dbms_metadata.get_ddl(object_type,object_name,user)
from DBA_objects
where ('&&objtype' is null or object_type = '&&objtype')
and owner = '&ownernm';
spool off
set feedback on
set verify on
set heading on
set pages 1000
undefine objtype
undefine ownernm
Cheers,
Vasan (x5707)
Mailpoint 28
Vasan Srinivasan * 020 8313 5707 Infrastructure Service Manager * 020 8313 5646
Oracle Technologies
Churchill Insurance, IT Department
Purple Floor, Phase 1, Churchill Court
1 Westmoreland Road,
Bromley, Kent, BR1 1DP.
Mobile * 07710 154 987
Views Presented here are not necessarily the views
of my Employer
============================================
-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM]
Sent: 30 April 2004 19:55
To: oracle-l_at_freelists.org
Subject: Re: Using dbms_metadata to extract a full schema
Thanks for all the code and packages. It looks like this is the
only way, I was just hoping that there was a method using the
dbms_metadata package to extract all the schema ddl with one
call. Of course, this would make sense and the documentation
SAYS you can do it, but it looks not to be the case.
Regards,
Daniel
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
This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426. England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.
 |
 |