Datapump SQLFILE parameter during Import

NOTE:   SQLFILE parameter works only for import(impdp) and should have dumpfile available.

For the past few days I have been dealing with the data pump export and imports for my Migration project on LINUX servers RHEL 5.4 and I learnt lot of new things after oozing out sweat. Today I created index creation DDL script with my existing datapump Dump files using SQLFILE parameter.

SQLFILE:  This parameter enables you to extract the DDL from the export/Import dump file, without Exporting/importing any data. SQLFILE includes DDLs for tablespaces, users, role grants, packages, procedures, functions, tables, indexes, primary and foreign keys, etc.

                               The INCLUDE parameter allows to target the DDLs you are interested in. for example if Specify INCLUDE=USER will give you CREATE USER statements.

SQL>  select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

Since here in this example shown I already have dumpfiles (Dump files which are created for schema refresh) and now I’m creating an index creation DDL script to create indexes on my schema tables

NOTE:   When you execute impdp with sqlfile option it won’t import the data into the actual tables or into the schema.

vi  ind_sqlfile.par

DIRECTORY=DPUMP_OMEGA_DIR1

DUMPFILE=DPUMP_OMEGA_DIR1:SYSFM_%U.dmp

LOGFILE=LOGFILE_OMEGA_dir1:create_ddl.log

PARALLEL=10

INCLUDE=INDEX

SCHEMAS=SYSFM

SQLFILE=createidx.sql

$ impdp sacorp/********  parfile=ind_sqlfile.par

Import: Release 11.2.0.2.0 - Production on Fri Feb 17 20:45:46 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters and Automatic Storage Management options

Master table "SACORP"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

Starting "SACORP"."SYS_SQL_FILE_SCHEMA_01":  sacorp/********  parfile=ind_sqlfile.par

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

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

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

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

Job "SACORP"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 20:56:50

Ran successfully and 'createidx.sql' file has created in the specified dump directory location.

Also Read:
Table_exists_action parameter
Datapump scenarios 
11g Datapump Parameters