ORA-39151: Table exists. All dependent metadata and data will be skipped due to table_exists_action of skip

I got ” ORA-39151: Table exists. All dependent metadata and data will be skipped due to table_exists_action of skip ”  error in Oracle database.

ORA-39151: Table exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Details of error are as follows.

Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=TEST_TABLE.dmp logfile=TEST_TABLE.log directory=DATAPUMP_DIR
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "MSDBA"."TEST_TABLE" 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" completed with 1 error(s) at Tue Jan 02 16:12:42 2021 elapsed 0 00:03:01

Table exists. All dependent metadata and data will be skipped due to table_exists_action of skip

This ORA-39151 error is related to the Table exists.

To solve this error, use table_exists_action parameter or REMAP_TABLE ( change different table name ) or REMAP_SCHEMA ( using different schema )

Table_exists_action: While Import process is running if any table exists in database what will Oracle do ? replace existing table or skip if it exists. this parameter can take following values.

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

SKIP is default value. If SKIP is used then table replacing is not done.

APPEND  loads rows from the export files and leaves target existing rows unchanged.

TRUNCATE deletes existing rows in target table and then loads rows from the export.

REPLACE drops the existing table in the target and then creates and loads it from the export.

For example: you run the following import command, If you don’t use table_exists_action parameter or use table_exists_action=skip, then Oracle will not perform table replacing when same tables exist  in the same schema. And the old tables and their data remain in the Original table.

the new table data= Old data and Old Metadata 

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=skip

If you use table_exists_action=APPEND option, then Oracle will load rows from the export files and leaves target existing rows unchanged.  Probably lots of data will be duplicated.

the new table data= Old data + Export data 

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=APPEND  

If you use table_exists_action=TRUNCATE, then Oracle will deletes ( truncate table ) existing rows in target table and then loads rows from the export.

the new table data= Only Export data 

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=TRUNCATE

If you use table_exists_action=REPLACE , then Oracle will drop the existing table in the target and then creates and loads it from the export.

the new table data= Export data and Export Metadata

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=REPLACE 

If you want to learn more details about Oracle Data pump, read the following post.

Do you want to learn Oracle Database for Beginners, then read the following articles.