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.