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.

* Vasan.Srinivasan@xxxxxxxxxxxxx

Mobile * 07710 154 987

http://oratech

============================================

Views Presented here are not necessarily the views

                          of my Employer

============================================ 

 

 

-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink@xxxxxxx] 
Sent: 30 April 2004 19:55
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx

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. 




----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: