Home

Introduction

When performing data import operations using Oracle Data Pump (impdp), conflicts may arise if the target table already exists. Unlike the conventional import utility (imp) that uses IGNORE=Y, Data Pump introduces the enhanced TABLE_EXISTS_ACTION parameter to define the handling of existing tables.

What is TABLE_EXISTS_ACTION?

The TABLE_EXISTS_ACTION parameter specifies how impdp should handle existing tables during the import process. The available options are:

• SKIP (Default): Ignores the existing table and skips importing data.
• APPEND: Adds new rows from the dump file to the existing table.
• TRUNCATE: Deletes existing rows in the table and then imports new rows.
• REPLACE: Drops the existing table and recreates it based on the dump file.

Examples of TABLE_EXISTS_ACTION in Action

1. SKIP (Default)

The SKIP option ignores the table if it already exists and avoids importing data. This is equivalent to the IGNORE=Y option in the conventional import utility.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip

Result: The table remains unchanged, and no data is imported. A message indicates the table was skipped due to the table_exists_action=skip parameter.

2. APPEND

The APPEND option adds rows from the dump file to the existing table without modifying the existing data.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append

Result: Rows in the dump file are appended to the existing table, retaining all previously existing data.

3. TRUNCATE

The TRUNCATE option deletes all rows from the existing table before importing data from the dump file.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate

Result: The table is truncated, and only the data from the dump file is present after the import.

4. REPLACE

The REPLACE option drops the existing table and recreates it based on the structure and data in the dump file.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace

Result: The existing table is dropped, and a new table is created with the same structure and data as in the dump file.

Best Practices

• Understand the Implications: Choose the appropriate TABLE_EXISTS_ACTION option based on the requirements of the import operation.
• Backup Data: Ensure the target table is backed up before using options like TRUNCATE or REPLACE.
• Verify Log Files: Always review the impdp log files for warnings or errors.
• Use SKIP for Safety: If unsure, start with SKIP to avoid unintended modifications to existing tables.

Conclusion

The TABLE_EXISTS_ACTION parameter in Oracle Data Pump Import provides flexibility in managing existing tables during the import process. Whether you need to append new data, replace existing tables, or simply skip the operation, this feature ensures that you have full control over your data import tasks. By understanding these options and their implications, you can handle complex import scenarios with ease.