Need DDL script to clone a schema 3

[SantaMufasa]

I invite the Great Oracle Minds of Tek-Tips to collaborate here to come up with the most efficient method of producing the SQL DDL code to reproduce all of the object structures of a schema. (We don't need to worry about rows of data, just the object structures' DDL.)

Amongst the restrictions are we cannot use export/import or Oracle data pump to produce the schema contents. The objective, again, is to produce DDL code that recreates all of the objects in a schema including:[ul][li]types[/li][li]sequences[/li][li]tables[/li][li]indexes[/li][li]triggers[/li][li]materialized views[/li][li]procedures[/li][li]functions[/li][li]packages and package bodies[/li][li]synonyms[/li][li]et cetera[/li][/ul]I highly recommend use of Oracle-supplied packages such as DBMS_METADATA and others. (I just wish there was a "DBMS_METADATA.CLONE_SCHEMA('<schema_name>')" procedure to do what I'm proposing in a single, clean invocation. Remember, this challenge must produce objects in such an order that reference objects upon which an object depends pre-exist: Example: our code must produce Parent Tables prior to Child Tables so that creation of child-table foreign keys will not fail due to the parent-table primary keys not yet existing.


I expect that this challenge/endeavor will end up being rather collaborative amongst contributors. But once we're done with this effort, I believe we will have an extremely valuable Oracle tool for each of our toolboxes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ggriffit]

Would any of the following work :

http://www.rampant-books.com/t_easy_html_db_generating_ddl.htm

http://www.perlmonks.org/?node_id=124397

http://www.udot.utah.gov/i/doc/ja/AEUTL/sql_utl_ddl.htm

http://www.club-oracle.com/forums/dbms_metadata-get_ddl-for-entire-schemas-scripts-t448/

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[Thargy]

Santa,

I do have a tool to do precisely what you ask. I use it to fully script an unknown database and for base lining builds.

Unfortunately, it consists of too many files to be posted here sensibly.

Any suggestions as to how to progress?

Regards

T

 

[SantaMufasa]

Thanks, Tharg. (BTW, Sorry that my silly cell phone did not complete your penultimate phone call to me. I promise I did not intend the call to go off in limbo. Then the second time you tried, I was on the phone to my doctor's office, and I could switch over to your call before it disappeared. Please forgive.)

I actually found a very nice solution...PL/SQL Developer (which I use frequently) has a Tools...Export all objects option that I had never noticed before. Therefore, I believe I'm good to go with that. (The only downside is that it puts the FK constraints immediately behind the CREATE TABLE commands, therefore, I had to move all the FK definitions down to the end of the CREATE TABLE commands to ensure that the parent tables exist before the child tables.

We can talk more about it if you want to give me one more chance on the phone.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Have you tried TOAD? I seem to remember that it also had a nice complete export and documentation set of features.

On a personal note, Santa, Nice to have you posting again..Your blog is keeping us informed, so best of luck...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

Thanks for the heads up on TOAD. Although many of my colleagues at work have/use TOAD, I haven't a TOAD license (and I resist using the test-drive version since I know I'm not going to migrate to the for-fee version).

Between TOAD, PL/SQL Developer, Tharg's scripts (which he has arranged for me to test drive next week), and other packaged routines, I can see that we won't need to reinvent the wheel here.

Thanks for your, and others, helpful input. Have

s for each of the helpful contributions here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

Check out DDL Wizard.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb