You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
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.
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.
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...
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?
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?
Creating Database Scripts - HOW?
Linear Mode
