How to Fix ORA-39181: Only Partial Table Data May Be Exported Due to Fine Grain Access Control in Oracle

When working with Oracle Data Pump (EXPDP), database administrators occasionally encounter a frustrating error message:

ORA-39181: Only partial table data may be exported due to fine grain access control

This issue typically appears when attempting to export a table protected by a Fine-Grained Access Control (FGAC) or Virtual Private Database (VPD) policy. Understanding why this error occurs and how to resolve it is crucial for maintaining consistent and secure exports, especially in environments with sensitive or restricted data.

For related topics, you can also check out our detailed guide on Oracle 19c Export and Import best practices

In this article, we’ll break down the root cause, scenarios, and effective solutions to fix the ORA-39181 error while following Oracle’s best practices.

---

What Causes ORA-39181 Error?

The ORA-39181 error is triggered during a Data Pump export operation when the exporting user is not exempt from Fine-Grained Access Control policies applied to the table.

Oracle’s FGAC, often used for data security, ensures that users only access rows they are authorized to view. When an export is attempted by a non-privileged user, Oracle restricts the operation to export only the rows visible to that user, resulting in the warning:

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

Essentially, the export doesn’t fail—it’s just incomplete, because the user doesn’t have the privileges to access the entire table data.

---

Understanding Fine-Grained Access Control (FGAC)

Fine-Grained Access Control (FGAC), also known as Virtual Private Database (VPD), allows administrators to enforce row-level security in Oracle. A policy function dynamically appends a WHERE clause to SQL queries, ensuring each user retrieves only the rows they’re authorized to see.

For example, a policy on a financial table might restrict each regional manager to view only their region’s data. While this improves data security, it can interfere with Data Pump exports, especially when non-administrative users attempt to export large or sensitive tables.

---

Scenario 1: Unprivileged User Export

Let’s consider an example to understand this error better.

• Table name: ABC_TAB
• Table owner: X_USER
• Partitioned: Yes
• Data size: ~500 GB

When the X_USER schema owner tries to export the table using:

expdp X_USER/password parfile=exp_ABC_TAB.par

The export log may show:

ORA-39181: Only partial table data may be exported due to fine grain access control

This occurs because the schema owner (though owning the table) is still restricted by the FGAC policy attached to it.

---

Scenario 2: Privileged User Export

Now, if a privileged user such as ORADBA (a DBA account) performs the same export:

expdp oradba/password parfile=exp_ABC_TAB.par

The export completes successfully:

Exported "X_USER"."ABC_TAB":"ABC_TAB_AA" 12340 KB   1000 rows
Primary table "ORADBA"."ABC_TAB_AA" successfully loaded/unloaded

Since the DBA account typically has EXEMPT ACCESS POLICY privilege, FGAC rules don’t apply, allowing the export to capture all table data.

---

Root Cause

This behavior is expected in Oracle.
The ORA-39181 message acts as an informational warning, not an error.

When a user without the EXEMPT ACCESS POLICY privilege attempts to export a table protected by FGAC or VPD, Oracle limits the export to the rows visible under that user’s access context. Consequently, only a subset of data is exported.

Oracle enforces this restriction to maintain data integrity and security policies. Allowing unrestricted exports without privileges would bypass security mechanisms.

---

Key Takeaway

If you’re a non-privileged user encountering ORA-39181 during a Data Pump export, remember this message is not a failure—it’s a security control in action.
However, if your intent is to perform a complete table export, administrative intervention is necessary.

---

Oracle’s Recommendation

Oracle strongly recommends that database administrators (DBAs) perform Data Pump exports for tables protected by FGAC policies.
This ensures the exported data is complete and consistent, avoiding partial or restricted data sets.

When a DBA performs the export, the process bypasses row-level restrictions while still preserving security policies for future imports.

---

Understanding EXEMPT ACCESS POLICY

The EXEMPT ACCESS POLICY privilege allows users to bypass FGAC and Oracle Label Security (OLS) policies during operations like queries, exports, and imports.

However, this is a powerful and sensitive privilege that must be granted carefully, as it allows access to all rows regardless of security restrictions.

To ensure proper control, Oracle suggests granting this privilege only to administrative roles, such as:

GRANT EXEMPT ACCESS POLICY TO exp_full_database;

This role is typically assigned only to DBA-level users, ensuring security policies remain enforced for regular users.

---

Important Exceptions

Even if a user has EXEMPT ACCESS POLICY, certain enforcement options remain active:

• INSERT_CONTROL
• UPDATE_CONTROL
• DELETE_CONTROL
• WRITE_CONTROL
• LABEL_UPDATE
• LABEL_DEFAULT

These options ensure that, while the user can bypass data visibility restrictions, integrity and labeling rules are still applied for security compliance.

---

How to Fix ORA-39181 Error

There are two safe ways to eliminate this message and ensure a full export:

Option 1: Grant EXEMPT ACCESS POLICY Privilege

If the exporting user requires full access, the DBA can grant the privilege:

GRANT EXEMPT ACCESS POLICY TO <exporting_user>;

This allows the user to export all rows from FGAC-protected tables.

However, this should only be done for trusted users who need administrative access.
Always review internal data governance policies before applying this change.

---

Option 2: Disable the FGAC (VPD) Policy Temporarily

If granting the privilege is not preferred, another approach is to temporarily disable the VPD policy before the export and re-enable it afterward.

You can disable the policy using the following command:

BEGIN
   DBMS_RLS.ENABLE_POLICY(
       object_schema => 'OBJECT_SCHEMA',
       object_name   => 'OBJECT_NAME',
       policy_name   => 'POLICY_NAME',
       enable        => FALSE
   );
END;
/

After completing the export, re-enable the policy:

BEGIN
   DBMS_RLS.ENABLE_POLICY(
       object_schema => 'OBJECT_SCHEMA',
       object_name   => 'OBJECT_NAME',
       policy_name   => 'POLICY_NAME',
       enable        => TRUE
   );
END;
/

This ensures your table remains protected but still allows a full export when necessary.

---

Best Practices

1. Always perform FGAC-protected exports as DBA users.
2. Document privileges before granting EXEMPT ACCESS POLICY.
3. Avoid granting FGAC exemptions to application or service accounts.
4. Re-enable policies immediately after maintenance or export tasks.
5. Audit all privileged actions to ensure compliance and accountability.

---

Conclusion

The ORA-39181: Only partial table data may be exported due to fine grain access control warning is not an error, but a security safeguard.
It indicates that Oracle’s fine-grained access controls are actively protecting sensitive data from unauthorized access during export operations.

To fix it, DBAs can either grant the EXEMPT ACCESS POLICY privilege or temporarily disable the FGAC policy during the export.
Both methods ensure complete and consistent data extraction without compromising security integrity.

By understanding this behavior and applying the right privileges carefully, you can maintain a secure yet efficient export process in your Oracle environment.