Extracting the DDL from a .dmp file

Instead of using the long syntax of an import, you can try a text search on the .dmp file to extract the DDL.

This Content Component encountered an error

In order to extract the DDL from a .dmp file, we normally use the INDEXFILE clause during import:

imp system/passwd@cntstring 
FILE=test.dmp 
INDEXFILE=scrip.txt
log=test.log

Instead of using the long import syntax you can try a text search on the .dmp file as follows. This is faster and simpler. It has been tested on Oracle 9i and 8i.

$fgrep 'CREATE'  test.dmp

Output 
------
CREATE TABLE "CDATA" ("CFID" CHAR(20), "APP" CHAR(64), "DATA" LONG)  PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "CLIENT" NOLOGGING NOCOMPRESS
CREATE UNIQUE INDEX "ID1" ON "CDATA" ("CFID" , "APP" )  PCTFREE 10 INITRANS
2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "CLIENT" LOGGING
CREATE TABLE "CGLOBAL" ("CFID" CHAR(20), "DATA" LONG, "LVISIT" DATE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "CLIENT" NOLOGGING NOCOMPRESS
CREATE INDEX "ID2" ON "CGLOBAL" ("CFID" )  PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "CLIENT"
LOGGING
CREATE INDEX "ID3" ON "CGLOBAL" ("LVISIT" )  PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "CLIENT"
LOGGING

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in October 2002

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close