Skip to content
Home » Oracle » How to Resolve impdp Error ORA-31693 with ORA-39116

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


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 *