Creating Database Scripts

gp_mk · June 7th, 2004, 09:10 AM

Does anyone know if it is possible to generate a script of an entire Oracle 9i database, similar to the SQL Server's "Generate SQL Script"? i.e. generate a script containing SQL commands to generate the database on another machine - something I could just run in SQL Plus, in the same way as you can do with SQL Server by running a script in Query Analyser.

I know that you can script an single database object from the EM by right clicking and selecting "Show Object DDL", but how do I do this for the entire database?

I am aware that you can export a database, but I need to generate a script of the structure. I cannot find any information anywhere on this topic. I have read about creating Templates and Baselines but I don't think they will give me what I want.

You can even script the differences between two databases from EM using the Compare Database function, but there is nothing I can find to script a single database.

Thanks for your help

Justin Cave · June 7th, 2004, 09:18 AM

If you are on 9i, you could use the dbms_metadata package and dynamic SQL to do this

Code:

DECLARE
  txt CLOB;
BEGIN
  FOR x IN (SELECT * FROM user_tables)
  LOOP
    txt := dbms_metadata.get_ddl( 'TABLE', x.table_name );
    <<do something with the DDL>>
  END LOOP;
END;

You should be able to get all the DDL by SELECT-ing from the dba_objects table.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

gp_mk · June 7th, 2004, 09:24 AM

The problem is there are lots of constraints on my tables, so if they aren't scripted in the correct order, the script will fall over when I try to run it. I could do it manually but my database is quite large so it would be very time consuming.

I also need to script all seqluences, triggers and packages - so it would be handy if there was something that cold do this automatically.

Coming from a SQL Server background, this sort of this is taken for granted...

Cheers,
Gary

Justin Cave · June 11th, 2004, 02:33 AM

What about taking an export of the database? If you specify rows=N, you'll get all the DDL but none of the data.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

gp_mk · June 11th, 2004, 02:48 AM

Thanks Justin.

I've just tried this from within the EM, by unchecking "Rows of table data". This seems to have generated a DMP file with the DDL inside. However it is not in a very readable format. Is there an easy way to extract the DDL from this file so that I can run it directly from SQL Plus?

Many thanks,
Gary

gp_mk · June 11th, 2004, 03:04 AM

I have also noticed that the DDL for my sequences has not reset the start values to 1. Is there any way I can get this to happen?

Thanks,
Gary

Justin Cave · June 11th, 2004, 03:32 AM

After doing the export

exp scott/tiger owner=scott rows=n

you can use the import utility to generate a human-readable SQL file

imp scott/tiger full=y indexfile=foo.sql

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

gp_mk · June 11th, 2004, 04:05 AM

Thanks Justin,

Actually, just having the export file containing the structure should be good enough. Do you know if there is a way to reset the start value of the sequences when producing an export?

Many thanks,
Gary

Justin Cave · June 11th, 2004, 04:11 AM

I don't believe so. You can write some dynamic SQL that alters the sequences to cycle, cycles them, and alters them back if it's really important.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC