TABLE_EXISTS_ACTION :
TABLE_EXISTS_ACTION is used in impdp when the table already exists in the database where the import is being executed.
TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}
- SKIP(default) leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY
- APPEND loads rows from the source and leaves existing rows unchanged.
- TRUNCATE deletes existing rows and then loads rows from the source.
- REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
SKIP:
[oracle@18c ~]$ impdp directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=skip Import: Release 18.0.0.0.0 - Production on Thu Aug 23 02:46:18 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=skip Processing object type TABLE_EXPORT/TABLE/TABLE Table "HARI"."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 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Aug 23 02:46:27 2018 elapsed 0 00:00:02
APPEND :
- Delete 1 row from employee table
SQL> conn hari/oracle Connected. SQL> select * from employee; ID SAL ---------- ---------- 1 5000 2 6000 3 7000 4 8000 5 9000 5 rows selected. SQL> delete employee where id=1; 1 row deleted. SQL> commit; Commit complete. SQL> select * from employee; ID SAL --- ------ 2 6000 3 7000 4 8000 5 9000 4 rows selected.
IMPDP :-
[oracle@18c ~]$ impdp system/oracle directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=append Import: Release 18.0.0.0.0 - Production on Thu Aug 23 02:21:09 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=append Processing object type TABLE_EXPORT/TABLE/TABLE Table "HARI"."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 "HARI"."EMPLOYEE" 5.515 KB 5 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Aug 23 02:21:19 2018 elapsed 0 00:00:04
Now 5 more rows append into the table.
SQL> select * from employee; ID SAL --- ------- 2 6000 3 7000 4 8000 5 9000 1 5000 2 6000 3 7000 4 8000 5 9000 9 rows selected.
TRUNCATE :-
In truncate option, it will truncate the content of the existing table and insert the rows from the dumpfile.
SQL> conn hari/oracle; Connected. SQL> select * from employee; ID SAL ---------- ---------- 2 6000 3 7000 4 8000 5 9000 1 5000 2 6000 3 7000 4 8000 5 9000 9 rows selected.
IMPDP:
[oracle@18c ~]$ impdp directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=truncate Import: Release 18.0.0.0.0 - Production on Thu Aug 23 02:39:21 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=truncate Processing object type TABLE_EXPORT/TABLE/TABLE Table "HARI"."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 "HARI"."EMPLOYEE" 5.515 KB 5 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Aug 23 02:39:30 2018 elapsed 0 00:00:04
SQL> conn hari/oracle; Connected. SQL> select * from employee; ID SAL --- ------ 1 5000 2 6000 3 7000 4 8000 5 9000
REPLACE:-
REPLACE option is to drop the current table in the database and the import recreate the new table as in the dumpfile.
[oracle@18c ~]$ impdp directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=replace Import: Release 18.0.0.0.0 - Production on Thu Aug 23 02:41:03 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Username: system/oracle Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=test_dir dumpfile=employee.dmp logfile=imp_employee.log table_exists_action=replace Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HARI"."EMPLOYEE" 5.515 KB 5 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Thu Aug 23 02:41:22 2018 elapsed 0 00:00:15
Check the table creation:-
SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='EMPLOYEE' and owner='HARI'; OBJECT_NAME CREATED -------------------- ------------------- EMPLOYEE 23-08-2018 02:41:09
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba