Extract SQL Statement while Import (Data pump )

Sqlfile  parameter of data pump specifies a file into which all of the SQL DDL that Import  would have executed, based    on other parameters .The   SQL is not  actually executed, and   the  target system   remains unchanged .Sqlfile   command is   only use in datapump import not in datapump  export. This   help   to investigate the schemas object and structure. Any existing file that has a name matching the one      specified with this parameter is overwritten.For Streams and other Oracle database options, anonymous PL/SQL blocks may appear within the SQLFILE output. They should not be executed directly.

syntax :    SQLFILE=[directory_object:]file_name

First of all we take the export of schemas and then we will import the dumpfile into another schema by using remap_schema command and extract the sql statements.Let's have a look on sqlfile parameter .

SQL> create directory DATAPUMP as 'd:\dpump\';

Directory created.

SQL> grant read,write on directory datapump to system;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>expdp system/ramtech@noida directory=datapump dumpfile=scott_sql.dmp logfile=sql_log.log schemas=scott

Export: Release 11.1.0.6.0 - Production on Thursday, 14 April, 2011 17:16:49

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=datapump dumpfile=scott_sql.dmp logfile=sql_log.log schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 384 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "SCOTT"."AAAA"                              5.039 KB       5 rows

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."TEST"                              5.023 KB       3 rows

. . exported "SCOTT"."TEST1"                             5.031 KB       3 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  D:\DPUMP\SCOTT_SQL.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:20:41

Now we will import the above dumpfile in another schemas(harry).

C:\>impdp system/ramtech@noida directory=datapump dumpfile=scott_sql.dmp logfile=impsql_log.log sqlfile=scott_sqlfile.sql   remap_schema=scott:harry

Import: Release 11.1.0.6.0 - Production on Thursday, 14 April, 2011 17:26:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/********@noida directory=datapump dumpfile=scott_sql.dmp logfile=impsql_log.log sqlfile=scott_sqlfile.sql remap_schema=scott:harry

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 17:27:17

Note:  Passwords are not included in the SQL file.           

 Enjoy      :-)