- 16 Jul, 2022
- 0 Comments
- 4 Mins Read
How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump
Syntax :
1 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIPImport: Release 19.0.0.0.0 - Production on Sat Jul 16 14:16:02 2022Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIPProcessing object type TABLE_EXPORT/TABLE/TABLETable "TESTUSER"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skipProcessing object type TABLE_EXPORT/TABLE/TABLE_DATAProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob "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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [oracle@test ~]$[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPENDImport: Release 19.0.0.0.0 - Production on Sat Jul 16 14:13:58 2022Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPENDProcessing object type TABLE_EXPORT/TABLE/TABLETable "TESTUSER"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of appendProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "TESTUSER"."ABC" 5.585 KB 6 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob "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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATEImport: Release 19.0.0.0.0 - Production on Sat Jul 16 14:15:12 2022Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATEProcessing object type TABLE_EXPORT/TABLE/TABLETable "TESTUSER"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncateProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "TESTUSER"."ABC" 5.585 KB 6 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob "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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACEImport: Release 19.0.0.0.0 - Production on Sat Jul 16 14:09:55 2022Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TABLE_01": "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACEProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "TESTUSER"."ABC" 5.585 KB 6 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:09:57 2022 elapsed 0 00:00:02[oracle@test ~]$ |