[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Generate Create Table Script for a Schema

Posted on 2008-01-29
8
Medium Priority
?
14,649 Views
Last Modified: 2013-12-18
Hi Experts:
In my schema, I have about 1500 tables. I would like to generate a "Create Table" script for all the tables in this schema. I tried using TOAD but it gets stalled.

Similarly, I would like a "Create View" script as well.

Lastly, A script that wold generate a script for ALL the Objects in the Schema.
0
Comment
Question by:KamalAgnihotri
8 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 20770163
I would use one of the Oracle packages...

DMBS_METADATA

So, you could do this:

SQL> spool ddl_script.sql
SQL> select dbms_metadata.get_ddl( 'TABLE', 'DEPT', 'SCOTT') FROM dual;
SQL> spool off;

If you want all the tables in a schema:

SQL> SELECT dbms_metadata.get_ddl( 'TABLE', table_name, user) FROM user_tables;
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20770209
Same true for VIEWS...

SELECT dbms_metadata.get_ddl( 'VIEW', view_name, user ) FROM user_views;
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20770232
I've not tried an "all objects" call to this package, but I imagine it might look like this:

SELECT dbms_metadata.get_ddl( object_type, object_name, user ) FROM user_objects;

... you might give it a try
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 8

Expert Comment

by:YANN0S
ID: 20770392
0
 

Author Comment

by:KamalAgnihotri
ID: 20776926
Hi Experts:
As suggested, I tried dbms_metadata.get_ddl    and I experineced strange behaviour.
here is the code.



0
 

Author Comment

by:KamalAgnihotri
ID: 20776969
SELECT DBMS_METADATA.GET_DDL ('TABLE','ABS_SECT')||';' FROM DUAL;

The above Query returns the code below.

  CREATE TABLE "MOMENTUM"."ABS_SECT"
   (      "CD" VARCHAR2(10) NOT NULL ENABLE,

It shows only one column in the table ABS_SECT. However the table has 5 columns.

Please reply with a solution ASAP.
0
 
LVL 9

Accepted Solution

by:
joebednarz earned 200 total points
ID: 20777055
I'm assuming you using SQL*Plus... in that case, you probably should do some things before running the query:

SET HEADING OFF;
SET PAGESIZE 100;
SET LONG 10000;

Then,

SELECT dbms_metadata.get_ddl( ....

The problem is the output is a long data type and by default long data output is limited to (I think) the first 100 characters....
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20779726
>> I tried using TOAD but it gets stalled

Try for smaller batches like 500 or 250 objects at a time and see if they complete.
Then run them from multiple instances of TOAD if you think they are doing pretty good.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question