Exp-00079解决方法
在执行exp导出用户某张表时候，遇到如下错误
--------------------------------------------------------------------------
EXP-00079: Data in table "ABCXXX" is protected. Conventional path may
only be exporting partial table.
. . exporting table
ABCXXX 6698 rows
exported
Export terminated successfully with warnings.
---------------------------------------------------------------------------
原因：这个警告出现是因为这张表启用了FGA（fine-grained access control policy）精细化访问控制策略。如果FGA在select上启用，那么exp可能不会导出整张表，因为FGA可能会重写查询。
只有sys用户和任何拥有exempt access policy权限的用户，才能select all rows。如果某个非sys用户没有exempt access policy权限，即使这张表是该用户的，也无法全表导出。
确认该表是否启用了FGA控制策略
SQL > conn / as sysdba
SQL > SELECT * FROM dba_policies where object_name=’ABCXXX’ and object_owner=’SCOTT’;
OBJECT_OWN OBJECT_NAME POLICY_GROUP POLICY_NAM PF_OWNER PACKAGE FUNCTION SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE LON
---------- -------------------- -------------------- ---------- --------------- -------------------- --------------- --- --- --- --- --- --- --- ---
SCOTT ABCXXX SCOTT ABCXXX SCOTT DBMS_CONTEXT FUN_GETPOLICY YES NO NO NO NO NO YES NO DYNAMIC NO
关于dba_policies中各字段的含义如下，重点看下粗字体内容
|
Column |
Datatype |
NULL |
Description |
|
OBJECT_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the synonym, table, or view |
|
OBJECT_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the synonym, table, or view |
|
POLICY_GROUP |
VARCHAR2(30) |
NOT NULL |
Name of the policy group |
|
POLICY_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the policy |
|
PF_OWNER |
VARCHAR2(30) |
NOT NULL |
Owner of the policy function |
|
PACKAGE |
VARCHAR2(30) |
|
Name of the package containing the policy function |
|
FUNCTION |
VARCHAR2(30) |
NOT NULL |
Name of the policy function |
|
SEL |
VARCHAR2(3) |
|
Indicates whether the policy is applied to queries on the object (YES) or not (NO) |
|
INS |
VARCHAR2(3) |
|
Indicates whether the policy is applied to INSERT statements on the object (YES) or not (NO) |
|
UPD |
VARCHAR2(3) |
|
Indicates whether the policy is applied to UPDATE statements on the object (YES) or not (NO) |
|
DEL |
VARCHAR2(3) |
|
Indicates whether the policy is applied to DELETE statements on the object (YES) or not (NO) |
|
IDX |
VARCHAR2(3) |
|
Indicates whether the policy is enforced for index maintenance on the object (YES) or not (NO) |
|
CHK_OPTION |
VARCHAR2(3) |
|
Indicates whether the check option is enforced for the policy (YES) or not (NO) |
|
ENABLE |
VARCHAR2(3) |
|
Indicates whether the policy is enabled (YES) or disabled (NO) |
|
STATIC_POLICY |
VARCHAR2(3) |
|
Indicates whether the policy is static (YES) or not (NO) |
|
POLICY_TYPE |
VARCHAR2(24) |
|
Policy type:
|
|
LONG_PREDICATE |
VARCHAR2(3) |
|
Indicates whether the policy function can return a maximum of 32 KB of predicate (YES) or not (NO). IfNO, the default maximum predicate size is 4000 bytes. |
解决这个问题的方法有两种
- 授权该用户exempt access policy 权限
（1） 在SQLPLUS 下以SYSDBA用户登录，授权在执行exp命令的用户 exempt access policy权限
SQL > conn / as sysdba
（2） 授权执行exp命令用户 exempt access policy 权限
SQL > grant exempt access policy to exp_user;
（3） 确认exp_user已经被成功授权
SQL >select grantee from dba_role_privs where granted_role in
(select grantee from dba_role_privs where granted_role=’EXP_FULL_DATABASE’)
好了，现在再次执行exp导出就不会报错了。
2. 以sys用户执行exp命令导出目标表(linux环境为例)
exp \‘/ as sysdba\‘ file=/app/dmp/abcxxx.dmp \
log=/app/dmp/abcxxx_exp.log \
tables=scott.abcxxx
