Extract Database DDL

1 – Workaround Import show=Y & indexfile=index.sql

imp show=Y

Importing with show=Y will list the contents of the file being imported, without actually performing any changes on the database.

imp userid=/ file=rdbm.dmp full=y show=y

more

imp indexfile=index.sql

Indexfile: This is another option with import (imp) utility to generate index DDL script from the export dump.  INDEXFILE option will spool the CREATE INDEX statements (including storage parameters) to the filename which specify in the indexfile. This will not generate primary key indexes.

2 – Workaround impdp(sqlfile=ddl.sql)

Question: In the old import utility we were able to use “show=y” to see the DDL contents of an export dump file. What is the equivalent in Data Pump?

Answer: In Data Pump import (impdp) made several great improvements:

The dump file is now in XML, and you can browser a dump file to see the contents.
The show=y has been replaced by the sqlfile argument.

$ impdp directory=expdir dumpfile=myexp.dmp sqlfile=ddl.sql

SQLFILE:
This parameter enables you to extract the DDL from the export/Import dump file, without Exporting/importing any data. SQLFILE includes DDLs for tablespaces, users, role grants, packages, procedures, functions, tables, indexes, primary and foreign keys, etc.
The INCLUDE parameter allows to target the DDLs you are interested in. for example if Specify INCLUDE=USER will give you CREATE USER statements.

http://snippetserver.com/?mode=article_detail&article=445
http://www.dba-oracle.com/t_data_pump_sqlfile_parameter.htm
http://chandu208.blogspot.in/2012/02/datapump-sqlfile-parameter-during.html

3 – Workaround Database API

DBMS_METADATA can be used to extract DDL definitions from a database:

GENERAL USAGE
set long 300000 pages 0 lines 140
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

select dbms_metadata.get_ddl('TABLE','EMP') from dual;
select dbms_metadata.get_dependent_ddl('INDEX','EMP') from dual;

https://sites.google.com/site/padipaco/generating-create-scripts-through-dbms_metadata-package

http://oracleandunix.blogspot.in/2009/07/dbmsmetadatagetddl.html

4 – Workaround Free Tool
SQL Developer
Export Oracle DDL to flat file using sql developer

We can export data from oracle to flat file using sql developer.
I hope who ever uses oracle have the tool sql developer, since it is a open source 🙂

Process
1) Select Tools in tabs and then select Database Export.
2) Select any file/location, default name is export.sql.
3) Select connection.
4) Select DDL option(Default are Terminator and Pretty Point)
5) Click Next
6) Select Object Types. If you don’t need data then uncheck data.
7) Click Next.
8 ) select GO.
9) Select tables or procedures or function or anything you want or select all and tehn click >>
10) Click Next
11) Now you can see export summary.
12) Click Finish.

You can have your ddl scripts backup.
http://perumal.org/sql-developer-database-export-wizard-to-export-ddl-and-data-as-dml/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.