Thursday, April 14, 2011

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      :-)


2 comments:

Shailesh Singh said...

Hello Sir, there is a minor mistake ...while importing you have used "remap_tablespace" instead of "remap_schema"....

NEERAJ VISHEN said...

Hi shailesh ,

Thanks .....