Home » SQL & PL/SQL » SQL & PL/SQL » Export schema with DBMS_metadata
Export schema with DBMS_metadata [message #234222] Mon, 30 April 2007 08:48 Go to next message
danijelv
Messages: 12
Registered: March 2007
Junior Member
Hi all,

I export whole schema with dbms_metadata. Export also tables with storage,schema name, tablespace.

I tried to use export from

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1464804639878#74720867341472

Could some help me to export hole schema,tables,view,.. without storage,tablespace and schema name. or what filter should i set?


Thanks for help,

Happy holidays

Regard Dani
Re: Export schema with DBMS_metadata [message #234241 is a reply to message #234222] Mon, 30 April 2007 10:10 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To omit storage parameters, tablespace, segment attributes:
Begin
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,
                                     'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'TABLESPACE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'SQLTERMINATOR', true);
  DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 
                                     'CONSTRAINTS_AS_ALTER', true);
END;
/

For schema name, I don't know any transformation but search in the doc maybe there is one.

Then use something like:
select DBMS_METADATA.GET_DDL(object_type,object_name,owner)
from dba_objects
where owner='<your user>'
  and object_type in ('<list of object types you want to get DDL>'
order by '<take care to create table before index...>'
/

Regards
Michel
Previous Topic: what is the purpose of some commands? and explain them
Next Topic: update statement behavior
Goto Forum:
  


Current Time: Sat Dec 03 10:18:46 CST 2016

Total time taken to generate the page: 0.08563 seconds