Ora-01031 将Flashback_time选项与EXPDP/IMPDP Network_link一起使用时权限不足(Doc ID 436106.1)

Ora-01031: Insufficient Privileges, When Using The Flashback_time Option with EXPDP/IMPDP Network_link (Doc ID 436106.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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.  通过远程连接(network_link或tns字符串)或以除SYS/SYSTEM之外的其他用户本地运行datapump expdp/impdp来从源(导出)实例导出/导入另一个schemas时，如果FLASHBACK_TIME或FLASHBACK_SCN用于获得一致的导出/导入。

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.  network_link引用有效的dblink，该链接已配置为作为已授予EXP_FULL_DATABASE的非SYS/SYSTEM schema 连接到源(导出)实例。

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  当您通过sqlplus以同一用户身份连接到导出实例时，可以查询导出表，但不能使用闪回查询来查询导出表

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

To use expdp/impdp FLASHBACK_TIME or FLASHBACK_SCN the user connecting to the instance must be a 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.   要使用expdp/impdp FLASHBACK_TIME或FLASHBACK_SCN，连接到实例的用户必须是对要导出/导入的对象授予闪回查询特权的用户。授予要在导出/导入期间访问的特定对象的FLASHBACK和SELECT特权，或授予FLASHBACK ANY TABLE特权以允许在所有表上进行导出/导入。

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.  当通过expdp/impdp network_link使用FLASHBACK_TIME或FLASHBACK_SCN时，授予network_link用户EXP_FULL_DATABASE和/或IMP_FULL_DATABASE角色不足以访问其他schema对象。

Note:
The ORA-31693 & ORA-1031 can also occur when exporting from a local database without using NETWORK_LINK if using the FLASHBACK_SCN or FLASHBACK_TIME options and the exporting user does not have FLASHBACK privileges granted on the tables being exported. See the Oracle® Database Advanced Application Developer's Guide 11g Release 2 (11.2) manual, chapter 12 'Using Oracle Flashback Technology', under the section entitled 'Configuring Your Database for Oracle Flashback Technology' for more information. 如果使用FLASHBACK_SCN或FLASHBACK_TIME选项并且未对导出的表授予FLASHBACK特权，则在不使用NETWORK_LINK的情况下从本地数据库导出时，也会发生ORA-31693和ORA-1031。有关更多信息，请参见《Oracle®数据库高级应用程序开发人员指南11g第2版（11.2）》手册的第12章“使用Oracle闪回技术”，在“为Oracle闪回技术配置数据库”部分下。

SOLUTION

Either configure the network_link or tns string user to connect to remote site as SYSTEM schema which has FLASHBACK ANY TABLE privilege or grant FLASHBACK ANY TABLE privilege to the user specified in network_link/tns configuration or doing local export.  配置network_link或tns字符串用户以具有FLASHBACK ANY TABLE特权的SYSTEM schema连接到远程站点，或者将FLASHBACKANY ANY TABLE特权授予在network_link / tns配置中指定的用户或进行本地导出。

On instance where expdp/impdp is being run:  在运行expdp/impdp的实例上：

create [public] database link <link_name> connect to <user on export instance> identified by <password> using '<tnsnames connect string>';

On instance where object is being exported or imported from:  在从中导出或导入对象的情况下：

grant EXP_FULL_DATABASE to <user named in exdp/imdp instance dblink>;

grant FLASHBACK ANY TABLE to <user named in expdp/impdp instance dblink>;