SQLFILE IN DATAPUMP 11g
Specifies a file into which all of the SQL
DDL that Import would have executed, based on other parameters, is written.
SQLFILE=[directory_object:]file_name
The file_name specifies where the import job will write the DDL that would be
executed during the job. The SQL is not actually executed, and the target
system remains unchanged. The file is written to the directory object specified
in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name
matching the one specified with this parameter is overwritten.
Note that passwords are not included in the SQL file. For example, if
a CONNECT statement
is part of the DDL that was executed, then it will be replaced by a comment
with only the schema name shown. In the following example, the dashes (--)
indicate that a comment follows, and the hr schema name is shown, but not the password.
-- CONNECT hr
Therefore, before you can execute the SQL file, you must edit it by
removing the dashes indicating a comment and adding the password for the hr schema.
For Streams and other Oracle database options, anonymous PL/SQL blocks
may appear within the SQLFILE output. They should not be executed directly.
- If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to
either ALL or DATA_ONLY .
- To
perform a Data Pump Import to a SQL file using Oracle Automatic Storage
Management (Oracle ASM), the SQLFILE parameter
that you specify must include a directory object that does not use the
Oracle ASM + notation. That is, the SQL file must be written to a disk
file, not into the Oracle ASM storage.
- The SQLFILE parameter cannot be used in conjunction
with the QUERY parameter.
Step1: Take the table backup of EMP table
[oracle@racs dp]$ expdp tables=EMP dumpfile=d1.dmp directory=dps
Export: Release 11.2.0.3.0 - Production on Mon Sep 23 09:56:44 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Produc
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DPUSR"."SYS_EXPORT_TABLE_01": dpusr/******** tables=EMP dumpfile=d1.d
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/STATISTICS/TABLE_STATISTICS
. . exported "DPUSR"."EMP" 8.578 KB 14 rows
Master table "DPUSR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSR.SYS_EXPORT_TABLE_01 is:
/u01/apps/home/oracle/dp/d1.dmp
Job "DPUSR"."SYS_EXPORT_TABLE_01" successfully completed at 09:57:28
step2: Before import you can generate all DDL transctions as a file.
You can open the file, check the tablespaces names and verify whether those tablespaces are exists or not. If not then remap the tablespace.
SEQUENCES:
As you know while importing if any object is there it will skip. If your application works based on sequence number sometimes target and source sequence number may not be matched. In this case you can drop the target sequences and create its .(To get the sequences from source database, you can see sqlfile.).
It saves lot of time. :)
You can open the file, check the tablespaces names and verify whether those tablespaces are exists or not. If not then remap the tablespace.
SEQUENCES:
As you know while importing if any object is there it will skip. If your application works based on sequence number sometimes target and source sequence number may not be matched. In this case you can drop the target sequences and create its .(To get the sequences from source database, you can see sqlfile.).
It saves lot of time. :)
[oracle@racs dp]$ impdp tables=emp dumpfile=d1.dmp directory=dps sqlfile=t1.sql remap_Table=emp:T1
Import: Release 11.2.0.3.0 - Production on Mon Sep 23 09:59:08 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01": dpusr/******** tables=emp dumpfile=d1.dmp directory=dps sqlfile=t1.sql remap_Table=emp:T1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:59:15
Note: Its not pointing about of number of records. This script only shows object structure not records.
Now you have two options to import i.e either run the t1.sql script under dpusr or impdp to import the records
Step 3 A: t1.sql script
[oracle@racs dp]$ sqlplus dpusr
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 23 10:00:12 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @t1.sql
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
T1 TABLE
TR VIEW
SQL> select * from t1;
no rows selected
Step3 B: Using impdp
[oracle@racs dp]$ impdp tables=emp dumpfile=d1.dmp directory=dps remap_Table=emp:T1
Import: Release 11.2.0.3.0 - Production on Mon Sep 23 10:01:27 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "DPUSR"."SYS_IMPORT_TABLE_02": dpusr/******** tables=emp dumpfile=d1.dmp directory=dps remap_Table=emp:T1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DPUSR"."T1" 8.578 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_IMPORT_TABLE_02" successfully completed at 10:01:36
Thank You and that i have a dandy proposal: How To Properly Renovate A House house renovation estimate calculator
ReplyDelete