Home » SQL & PL/SQL » SQL & PL/SQL » Reverse Engineer Schema (Oracle 10, AIX)
Reverse Engineer Schema [message #409811] Wed, 24 June 2009 03:04 Go to next message
kheat
Messages: 4
Registered: November 2008
Junior Member
Hi All,

We require a script to extract all the objects in a schema and store it in a folder wise sequence.
ie.
On running the procedure/package the output would be like

<SchemaName>
<Tables>
<SchemaName>.<TableName1>
<SchemaName>.<TableName2>
<Views>
<Indexes>
and so on.

Basically every file would be the create script for that object.



Re: Reverse Engineer Schema [message #409813 is a reply to message #409811] Wed, 24 June 2009 03:11 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Look at the DBMS_METADATA package. You are unlikely to get someone to just give you the full answer here. At OraFAQ we tend to look to you to do some/most of your own work, and offer support and advice when you get stuck. Much of the support and advice constitutes pointing you towards the appropriate documentation or towards useful keywords to search for.

Good luck. And remember, do ask questions when you run across problems but bear in mind you might not always get the exact answer back.
Re: Reverse Engineer Schema [message #409818 is a reply to message #409811] Wed, 24 June 2009 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Look into DBMS_METADATA.GET_DDL

You can very nearly do
SELECT dbms_metadata.get_ddl(object_type,object_name)
FROM   user_objects
but some of the object types that DBMS_METADATA uses don't quite match with the ones from User_Objects.
Re: Reverse Engineer Schema [message #409993 is a reply to message #409811] Wed, 24 June 2009 14:52 Go to previous messageGo to next message
kheat
Messages: 4
Registered: November 2008
Junior Member
How will we be able to extract this folderwise? We understand that using the dbms_metadata.get_ddl would allow us to get the objects source code but how do we create a single script which would run through the schema and store them in a directory listing mode?
Re: Reverse Engineer Schema [message #410009 is a reply to message #409811] Wed, 24 June 2009 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Depending upon actual implementation either SPOOL or UTL_FILE can be used to create container SQL file.
Re: Reverse Engineer Schema [message #410264 is a reply to message #410009] Thu, 25 June 2009 22:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
you could also dump the data as XML and let the nesting of your queries do the "container" thing for you. But this is getting extravagant I think.

You might also wish to spend some money on a tool of see if you do not already have a tool available to you that will do this for you. Think TOAD, or any of the data modeling tool like ERSTUDIO or [gulp] Erwin.

Good luck, Kevin
Re: Reverse Engineer Schema [message #410372 is a reply to message #409993] Fri, 26 June 2009 10:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could just output objects into names that made it clear what soert of object they were, and move them by hand.

Is it me, or does doing actual work seem to be getting unfashionable these days?
Previous Topic: file creation
Next Topic: insufficient privs error
Goto Forum:
  


Current Time: Sat Nov 02 12:46:36 CDT 2024