convert expdp dmp file to SQL DDL statements

convert expdp dmp file to SQL DDL statements

Oracle Database Tips by Donald BurlesonApril 7, 2016

Question:  I want to take an export dmp file and convert it into the native SQL statements.  Can I convert a dmp file using the sqlfile option?

Answer:  You cannot convert an existing dmp file because of its internal format, but you can re-run the impdp to get the SQL DDL statements from a Data Pump dmp file with this sqlfile command:

impdp scott/tiger directory=dir_name dumpfile=myfile sqlfile=xyz.sql

This sqlfile option will create a myfile file in the dir_name directory with the DDL statements.

Here is an example of using the sqlfile parameter with impdp to display the DDL within an Data Pump Export (expdp) file named myexp.dmp:

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

In this example, the impdp does not actually perform an import of the contents of the dump file.  Rather, the sqlfile parameter create a script named ddl.sql that contains all DDL from within the export dump file.

Also see how using the sqlfile parameter is useful when gathering arguments for the dbms_datapump metadata_remap procedure.