oracle11g用户system使用expdp导出报错ORA-39181

# 环境

操作系统

$ uname -a

Linux jinhu-db02 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release 

CentOS release 6.9 (Final)

数据库

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

1. expdp导出日志

[oracle@test-oracle db_transport]$ /home/oracle/db_transport/db_expdp_full.sh

Export: Release 11.2.0.4.0 - Production on Fri Apr 13 14:41:41 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@ORCL DIRECTORY=exp_dir DUMPFILE=expdp_full_20180413.dmp FULL=y LOGFILE=expdp_full_20180413.log 

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 375.6 MB

.................

ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER"

. . exported "OE"."PURCHASEORDER"                        243.9 KB     132 rows

.................

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

  /home/oracle/exp_dir/expdp_full_20180413.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Fri Apr 13 14:44:30 2018 elapsed 0 00:02:47

ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHASEORDER"

错误的原因是一个没有权限的用户试图导出一个具有细粒度访问控制的表。该表的拥有者访问该表会受限，因此不能导出该表里的所有行。

该表的所有者只能导出其能看到的那些表行。因此，要保证表的完整性，在安全策略的前提下导入该表时，导入该表的用户必须拥有足够的权限来重建该表。

解决的办法就是将EXEMPT ACCESS POLICY这个权限赋给system用户：

[oracle@test-oracle exp_dir]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 13 14:54:56 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> GRANT EXEMPT ACCESS POLICY TO system;

Grant succeeded.

[oracle@test-oracle exp_dir]$ /home/oracle/db_transport/db_expdp_full.sh

Export: Release 11.2.0.4.0 - Production on Fri Apr 13 15:00:50 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@ORCL DIRECTORY=exp_dir DUMPFILE=expdp_full_20180413.dmp FULL=y LOGFILE=expdp_full_20180413.log 

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 375.6 MB

........................

. . exported "PM"."PRINT_MEDIA"                          190.3 KB       4 rows

. . exported "OE"."PURCHASEORDER"                        243.9 KB     132 rows

. . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.8 KB   11266 rows

........................

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

  /home/oracle/exp_dir/expdp_full_20180413.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Fri Apr 13 15:03:39 2018 elapsed 0 00:02:48

!!The End!!