Cannot Exchange partitions between Oracle schemas. ALTER TABLE EXCHANGE restricted by fine-grained security. Realm violation...EXEMPT ACCESS POLICY

Question

I have a working Oracle procedure that does the following:

1. Chooses a partition from a Source table
2. Inserts records from this partition into a Swap table. (design/structure of Swap table matches Source)
3. Modifies records in the Swap table.
4. Does an "exchange partition" to swap the records from the Swap Table back to the Source.

This works perfectly when the procedure and all tables are in the same schema: (INBOUND)

However... the next step is to have it use Source tables that exist in another Schema: WAREHOUSE

It now fails on the Exchange Partition step:

Alter Table WAREHOUSE.ITEM_FCT Exchange Partition SYS_ABCD WITH TABLE INBOUND.ITEM_SWP Including     
Indexes Without Validation;

SQL Error: ORA-14136: ALTER TABLE EXCHANGE restricted by fine-grained security 14136. 00000 - "ALTER TABLE EXCHANGE restricted by fine-grained security" *Cause: User doing exchange does not have full table access due to VPD policies. *Action: Grant exempt priviliges to this user.

I attempted to add the permissions needed, based on google research:

grant ALL on INBOUND.ITEM_SWP TO WAREHOUSE;
grant EXEMPT ACCESS POLICY to INBOUND;

When I try the grant exempt access policy, it says:

grant EXEMPT ACCESS POLICY to INBOUND
Error report -
SQL Error: ORA-47410: Realm violation for GRANT on EXEMPT ACCESS POLICY

I would greatly appreciate any guidance. Placing everything in the WAREHOUSE schema would be an absolute last resort.

Thanks!

Answer 1

You can't apply exchange partition operation for table with FGAC policy enabled. This restriction is described in Oracle reference. You need to disable policy, apply operation and re-enable it again. However this is not ideal cause for some short period of time your data will be not protected by policy.