Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

IMPDP UTILITY :- TABLE_EXISTS_ACTION =OPTIONS IN ORACLE

SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y (If a table already exists and IGNORE=y, then rows are imported into existing tables without any errors or messages being given 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.

Method to Import only rows does not exist in the target table 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.

1.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

2.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.

3.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

4.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

Comments

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Configuration UDEV SCSI Rules In Oracle Linux 5, 6 and 7

Linux ctime,mtime,atime,cmin,amin,mmin

aspell