advertisements
_____________________________________________________________________________________________________________________
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name. When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.
- SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.
- APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.
- TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
- REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.
See some examples here.
This is my sample table employee
SQL> select * from employee;
EMP_NAME DEPT SAL
------------------------------ ---------- ----------
Rupal 10 5000
Hero 10 5500
Jain 10 4000
John 20 6000
I took the data pump dump for employee table.
$ expdp directory=exp_dir tables=scott.employee dumpfile=emp.dmp logfile=emp.log
Export: Release 11.2.0.2.0 - Production on Tue May 1 23:31:04 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=exp_dir tables=scott.employee dumpfile=emp.dmp logfile=emp.log
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
. . exported "SCOTT"."EMPLOYEE" 5.953 KB 4 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/shony/emp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:20
Now Let’s try each options.
- TABLE_EXISTS_ACTION=SKIP
$ impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=skip
Import: Release 11.2.0.2.0 - Production on Tue May 1 23:36:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 23:36:13
- TABLE_EXISTS_ACTION=APPEND
I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.
SQL> delete from employee;
4 rows deleted.
SQL> insert into employee (select * from emp where dept>20);
4 rows created.
SQL> commit;
SQL> select * from employee;
EMP_NAME DEPT SAL
------------------------------ ---------- ----------
Kiran 30 5500
Peter 30 6800
King 30 7600
Roshan 30 5500
$ impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=append
Import: Release 11.2.0.2.0 - Production on Wed May 2 00:50:18 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPLOYEE" 5.953 KB 4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:50:25
Now 4 more rows appended to the table.
1* select * from employee
SQL> /
EMP_NAME DEPT SAL
------------------------------ ---------- ----------
Kiran 30 5500
Peter 30 6800
King 30 7600
Roshan 30 5500
Rupal 10 5000
Hero 10 5500
Jain 10 4000
John 20 6000
8 rows selected.
- TABLE_EXISTS_ACTION=TRUNCATE
Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my employee table has 8 rows which we inserted last insert.
$ impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=truncate
Import: Release 11.2.0.2.0 - Production on Wed May 2 00:55:03 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPLOYEE" 5.953 KB 4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:55:09
1* select * from employee
SQL> /
EMP_NAME DEPT SAL
------------------------------ ---------- ----------
Rupal 10 5000
Hero 10 5500
Jain 10 4000
John 20 6000
- TABLE_EXISTS_ACTION=REPLACE
This option drop the current table in the database and the import recreate the new table as in the dumpfile.
impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace
Import: Release 11.2.0.2.0 - Production on Wed May 2 00:57:35 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPLOYEE" 5.953 KB 4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:57:40
Now if you check the last_ddl_time for the table it would be the same as the import time.
$ date
Wed May 2 00:58:21 EDT 2012
select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='EMPLOYEE'
SQL> /
OBJECT_NAME CREATED
-------------------- -------------------
EMPLOYEE 02-05-2012 12:57:40
Datapump Tuning Features
Datapump vs EXP/IMP
Datapump Exclude Table Partition
Datapump Tuning Features
Datapump vs EXP/IMP
Datapump Exclude Table Partition
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment