Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 7th, 2004, 09:10 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating Database Scripts - HOW?

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
Reply With Quote
  #2 (permalink)  
Old June 7th, 2004, 09:18 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 7th, 2004, 09:24 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old June 11th, 2004, 02:33 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 11th, 2004, 02:48 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old June 11th, 2004, 03:04 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old June 11th, 2004, 03:32 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old June 11th, 2004, 04:05 AM
Authorized User
 
Join Date: Nov 2003
Location: , , United Kingdom.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old June 11th, 2004, 04:11 AM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a database with scripts hericles ASP.NET 1.x and 2.0 Application Design 1 August 31st, 2007 08:37 PM
creating a database kcrist84 VB Databases Basics 3 July 20th, 2007 03:35 PM
Creating New Database everest SQL Server 2005 2 July 10th, 2007 01:32 PM
Creating a database login Apocolypse2005 PHP Databases 3 January 6th, 2006 12:59 PM
problem creating database nusrati MySQL 15 July 6th, 2004 10:46 AM



All times are GMT -4. The time now is 04:24 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.