create DDL from data dictionary tables. 1

[malva]

Does anyone know how to extract the DDL to edit and create the database structures from the data dictionary tables. This seems very basic, but I am new to Oracle and i cannot find a feature that is like Sql Server's script database capability.

 

[carp]

Try using the EXPORT utility without exporting any of the data. The dump file will contain the SQL to recreate to database objects.

 

[THoey]

Also, if you have Oracle 8.1.7, the DBA Studio now has a tool that will create the DDL and allow you to save it to file. I do not believe it was available in 8.1.6. Terry M. Hoey

 

[THoey]

I meant to add that to use this tool, right click on an object and select Create DDL (I think that is the option) Terry M. Hoey

 

[carp]

Nice trick! Thanks, Terry!

 

[carp]

I guess if you want a specific table's DDL, you should use Terry's approach.
If you want multiple objects (e.g., all of the tables and indexes in a schema), I would still use EXPORT.

 

[malva]

This forum is great, I did not get this response on Oracle's forums. Does the export feature save the DDL in a file that is editable? This is the point. On Personal Oracle 7.3 it was not. I recently installed Personal Oracle 8i and cannot find the export utility to check it out. I have the impression that I need to install the Oracle Management Server to get this utility and I have not been able to figure out what it needs for the install. There is always something! Personal Oracle 8i seems very bloated and complicated for something that is suppose to be for desktop use.

 

[rcurva]

The output file can be edited, for example if you want to change initial extent size before you import back. But this practice is not supported by Oracle and must be done with care (like backup the dmp file first).

 

[carp]

Parts of the dump file are editible, other parts aren't. Fortunately, the part you want is. Like rcurva said, I would save the dump file (you've already done the hard part in creating it, you might as well save it - it may come in handy!), make a copy, then edit out the binary parts with the text editor of your choice. What remains will be your DDL statements in a text file.

 

[JFlack]

While EXP can be used to save DDL, and you can use IMP to write that DDL to a file, the DDL gets chopped up, and it takes a bit of editing to get it into executable form.

There are many good SQL*Plus scripts for doing this, and many of them are freely available for download. For instance, one nice collection is on the DBA SIG section of the New York Oracle Users Group web site -

http://www.nyoug.org.

Another good source is from Oracle Technical Network - technet.oracle.com - you have to register for technet, but it is free, and well worth it. Oracle Magazine also has scripts available for download -

http://www.oramag.com.

If you have Pro*C and a C compiler, you can try my C program for writing DDL - makecrtb.pc, available from

http://www.smdi.com/employee/johnf.

 

[JFlack]

I just noticed that this BBS tries to turn my web addresses into links, and doesn't do a great job. Here they are again:

NY OUG -

http://www.nyoug.org

Technet -

http://technet.oracle.com

Oracle Magazine -

http://www.oramag.com

My web site -

http://www.smdi.com/employee/JohnF/orastuff.htm

 

[sueinthewind]

I wrote my own for use with DB2.