在执行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