Import / export datapump using FLASHBACK_TIME or FLASHBACK_SCN ( and NETWORK_LINK ) fails with ORA-31693, ORA-01031

I’m writing a shellscript that loads a remote schema into my database using impdp network_link – all goes well. Only once I add the FLASHBACK_TIME I got error below;

ORA-31693: Table data object "<TABLE_OWNER>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-02063: preceding line from <DATABASE_NAME>

Here is my impdp command;

${ORACLE_HOME}/bin/impdp                                                \
    JOB_NAME=${_IMPDP_JOBNAME}                                            \
    NETWORK_LINK=${OPT_DB_SOURCE}                                         \
    SCHEMAS=${OPT_DB_SCHEMA}                                              \
    PARALLEL=${_DB_IMPDP_PARALLEL}                                        \
    NOLOGFILE=Y                                                           \
    FLASHBACK_TIME=systimestamp                                           \


  echo "IMPDP exit code = ${_impdp_return_code}";

  if [ ! ${_impdp_return_code} -eq 0 ] ; then
    program_error ${ERR_DATAPUMP_IMP} ${_IMPDP_JOBNAME}

What? My database link user (username of database / network_ link OPT_DB_SOURCE) – does have the EXP_FULL_DATABASE system privilege already on the database it connects to.

Luckily this one has an easy fix, as per metalink note; Doc ID 436106.1 – Ora-01031: Insufficient Privileges, When Using The Flashback_time Option with EXPDP/IMPDP Network_link

User needs FLASHBACK (any) table privilege.

To use expdp/impdp FLASHBACK_TIME or FLASHBACK_SCN the network_link must connect to the remote instance as user which has flashback query privileges granted on object to be exported/imported. Grant FLASHBACK and SELECT privileges on specific objects to be accessed during export/import or grant the FLASHBACK ANY TABLE privilege to allow export/import on all tables.

Granting the network_link user EXP_FULL_DATABASE and/or IMP_FULL_DATABASE role is not sufficient to access other schema objects when using FLASHBACK_TIME or FLASHBACK_SCN via expdp/impdp network_link.

OK – so here’s the fix;

SQL> grant flashback any table to <username>
  2  /

Grant succeeded.

All the above applies to expdp with flashback_scn or flashback_time as well.

2 thoughts on “Import / export datapump using FLASHBACK_TIME or FLASHBACK_SCN ( and NETWORK_LINK ) fails with ORA-31693, ORA-01031

  1. After giving flashback privilige i get the same error insufficient privilige when take full db export .when export individually no error

Leave a Reply

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