Ora-01031: Insufficient Privileges, When Using The Flashback_time Option with EXPDP/IMPDP Network_link

(Doc ID 436106.1)

Last updated on MARCH 21, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 12-Apr-2013***

Symptoms

When running datapump expdp/impdp via remote connection (network_link or tns string) or locally as another user other than SYS/SYSTEM to export/import another schemas data from the source (export) instance, the process fails with following errors if the FLASHBACK_TIME or FLASHBACK_SCN is used to get a consistent export/import.

ORA-31693: Table data object "SCOTT"."FLSHBK_EMP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-02063: preceding line from FLASH_BK_EXPDP

The network_link references a valid dblink which is configured to connect to remote (export) instance as a non SYS/SYSTEM schema that has been granted EXP_FULL_DATABASE.

When you connect to export instance as this same user via sqlplus you can query the export table but can not query it using flashback query

SQL> connect <non SYS/SYSTEM user>/<password>

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

SQL> select count(*) from scott.emp as of timestamp(sysdate);
select count(*) from scott.emp as of timestamp(sysdate)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms