Data Pump EXPDP Error: ORA-39181: Only partial table data may be exported due to fine grain access control on


Error Description:
Data pump expdp log shows following error for some tables during the export.
ORA-39181: Only partial table data may be exported due to fine grain access control on "SCOTT"."CONTROL_ITEMS"


Solution Description:
This error occurs when a unprivileged user tries to export the tables which are enabled with Fine Grain Access Control policies. You can verify these kind of tables using the below query.

select * from dba_policies where OBJECT_OWNER='SCOTT' AND OBEJCT_NAME=’CONTROL_ITEMS’


It is recommended that the database administrator has to handle the export dump. Or to access such tables the user must have the EXEMPT ACCESS POLICY privilege. Once you give above privilege to the user then the export will work as normal. If it is one time export from the particular user as administrator you can disable the VPD policy and re-enable the policy again once the export completes.

grant EXEMPT ACCESS POLICY to scott;
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google