Data Pump

How to Resolve impdp Error ORA-31693 with ORA-39116

ORA-31693

Got ORA-31693 when import data with schema mode.

[oracle@test ~]$ impdp system/password schemas=hr,oe,sh content=data_only table_exists_action=truncate network_link=system_link_orcl
...
ORA-31693: Table data object "OE"."ORDER_ITEMS" failed to load/unload and is being skipped due to error:
ORA-39116: invalid trigger operation on mutating table OE.ORDER_ITEMS
...

In practice, we disable all related triggers before data pump for avoiding trigger errors and faster import jobs.

Here is my sample script for disabling all related triggers.

SQL> set heading off feedback off pagesize 0 linesize 150 echo off
SQL> spool /home/oracle/disable_triggers.sql
SQL> select 'alter trigger "' || owner || '"."' || trigger_name || '" disable;' stmt from dba_triggers where table_owner in ('HR', 'OE', 'SH') and status = 'ENABLED' order by owner, trigger_name;
SQL> spool off
SQL> exit

Modify the statements and save a copy for enabling statements later.

[oracle@test ~]$ vi disable_triggers.sql
[oracle@test ~]$ cp -p disable_triggers.sql enable_triggers.sql
[oracle@test ~]$ vi enable_triggers.sql

Then execute the SQL script.

SQL> @/home/oracle/disable_triggers.sql

This can prevent ORA-31693 from being thrown. Now, you can import the data again. And don’t forget to enable them back once import is completed.

SQL> @/home/oracle/enable_triggers.sql

Additionally, you may need to disable referential constraints before import as well.

If you don’t want to disable triggers or constraints, you should use table_exists_action=replace to dump the data instead.

Leave a Reply

Your email address will not be published. Required fields are marked *