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

  • Published on Aug 29 2013
  • # 4,756
  • oracle

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                                                \
    USERID=${_DB_IMPDP_USERNAME}/${_DB_IMPDP_PASSWORD}@${OPT_DB_TARGET}   \
    JOB_NAME=${_IMPDP_JOBNAME}                                            \
    NETWORK_LINK=${OPT_DB_SOURCE}                                         \
    SCHEMAS=${OPT_DB_SCHEMA}                                              \
    PARALLEL=${_DB_IMPDP_PARALLEL}                                        \
    NOLOGFILE=Y                                                           \
    FLASHBACK_TIME=systimestamp                                           \
    KEEP_MASTER=Y

  _impdp_return_code=$?

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

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

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. Andy Stonham says:

    Just what I needed to fix my impdp using network_link and flashback, thanks

  2. sarvesh says:

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

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>