- 16 Jul, 2022
- 0 Comments
- 4 Mins Read
How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump
Syntax :
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
The possible values have the following effects:
- SKIP 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.
TABLE_EXISTS_ACTION=SKIP
This is the defult option with impdp. I.e if the the table exists, it will skip that table.
[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIP Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:16:02 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIP Processing object type TABLE_EXPORT/TABLE/TABLE Table "TESTUSER"."ABC" 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 "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:16:03 2022 elapsed 0 00:00:01 [oracle@test ~]$
TABLE_EXISTS_ACTION=APPEND:Â Â With this option, while importing the table, if the table exists in the database, then it will append the data on top the existing data in the table.
[oracle@test ~]$ [oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPEND Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:13:58 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPEND Processing object type TABLE_EXPORT/TABLE/TABLE Table "TESTUSER"."ABC" 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 "TESTUSER"."ABC" 5.585 KB 6 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:14:00 2022 elapsed 0 00:00:02 [oracle@test ~]$
TABLE_EXISTS_ACTION=TRUNCATE:
While importing the table, if the table exists in database, it will truncate the table and load the data.
[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATE Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:15:12 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATE Processing object type TABLE_EXPORT/TABLE/TABLE Table "TESTUSER"."ABC" 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 "TESTUSER"."ABC" 5.585 KB 6 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:15:13 2022 elapsed 0 00:00:01 [oracle@test ~]$
TABLE_EXISTS_ACTION=REPLACE:
While importing , if the table exists in database, then it will drop it and recreate it from the dump.
[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACE Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:09:55 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TESTUSER"."ABC" 5.585 KB 6 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:09:57 2022 elapsed 0 00:00:02 [oracle@test ~]$