Generate sql file from datapump export

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option.



oracle@Linux01:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST

Export: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:00 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."TEST"                            5.875 KB       5 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/test1.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 3 15:22:13 2016 elapsed 0 00:00:07

oracle@Linux01:[/u01/app/oracle/dpump] $ ls -ll test1.dmp
-rw-r-----. 1 oracle oinstall 114688 Feb  3 15:22 test1.dmp
oracle@Linux01:[/u01/app/oracle/dpump] $ impdp atoorpu directory=DPUMP dumpfile=test1.dmp sqlfile=TEST1.sql

Import: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:35 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_SQL_FILE_FULL_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp sqlfile=TEST1.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Feb 3 15:22:42 2016 elapsed 0 00:00:01

oracle@Linux01:[/u01/app/oracle/dpump] $ ls -ltr
-rw-rw-r--. 1 atoorpu atoorpu      18870 Feb  3 09:38 test.log
-rw-r--r--. 1 oracle  oinstall      1325 Feb  3 15:22 test1.log
-rw-r-----. 1 oracle  oinstall    114688 Feb  3 15:22 test1.dmp
-rw-r--r--. 1 oracle  oinstall      3800 Feb  3 15:22 TEST1.sql
-rw-r--r--. 1 oracle  oinstall       933 Feb  3 15:22 import.log

If you try to use SHOW=Y you wil see below message in the output.

oracle@Linux01:[/u01/app/oracle/dpump] $ impdp atoorpu dumpfile=test1.dmp SHOW=Y

Import: Release 11.2.0.4.0 - Production on Wed Feb 3 15:17:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "show=TRUE" Location: Command Line, Replaced with: "sqlfile=test1.sql"
Legacy Mode has set nologfile=true parameter.