Get DDL from EXPDP dumpfile.

Arju

Published on 11-08-2008 10:44 AM

With parameter sqlfile=file_name you can easily get DDL from dumpfile.
Following is the steps.

1.Create directory where dump resides.

Code :

create directory d as 'E:';

2.Now perform impdp. Note that no actual rows will be imported. Only you will get the file includes the DDL inside dump.

Code :

impdp system/s sqlfile=1.log dumpfile=test.dmp directory=d

With above command you will get DDL command inside the 1.log of dump file test.dmp
Now if you look at 1.log you will get output as,

Code :

-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
 CREATE USER "CR_2" IDENTIFIED BY VALUES '754F6AD69455CF37'
      DEFAULT TABLESPACE "CR_SPC"
      TEMPORARY TABLESPACE "TEMP";
 
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "CR_2";
 
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "CR_2";
 
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "CR_2" DEFAULT ROLE ALL;
 
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT CR_2
 
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'SKYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM', inst_scn=>'1264232148');
COMMIT; 
END; 
/ 
 CREATE SEQUENCE  "CR_2"."ADMIN_SQ"  MINVALUE 1 MAXVALUE 
999999999999999999999999999 INCREMENT BY 1 START WITH 3 NOCACHE  NOORDER  NOCYCLE ;
 
 CREATE SEQUENCE  "CR_2"."BENING_ACCOUNTING_ID_SQ"  MINVALUE 1 MAXVALUE 
999999999999999999999999999 INCREMENT BY 1 START WITH 8222517 NOCACHE  NOORDER  NOCYCLE ;
 
.
.
.

Get a Free Complementary Copy of Oracle Magazine

You can also browse the Free Magazines and E-Books section to see the complete list of free magazines, e-books and Whitepapers.

1 Comment

Arju - 11-08-2008
In above post I have showed how we can get DDL from dumpfile that was taken by EXPDP. Now I will show how we can get DDL from dumpfile that was taken by EXP.

The dump that was taken by EXP, has several ways to see DDL inside it.

Way 1: Using strings command in unix:
If you are on unix machine then the strings tool helps a lot to get DDL inside dump. suppose my dump file is test.dmp then use,
Code :
strings test.dmp
Way 2: Using show clause.
Import command will be,
Code :
imp userid=username/password file=test.dmp show=y
In your screen you will see see the DDL.

Way 3:Using indexfile clause.
With indexfile clause you can get a fine output inside your file. Use it like,
Code :
imp userid=username/password file=test.dmp indexfile=a.sql
In the file a.sql you will get your desired DDL.
Reply

Get DDL from EXPDP dumpfile.

Article Categories