ORA-30372 fine grain access policy conflicts with materialized view

Question

I'm trying to create Materialized view which will run every 6 hour at DB1, and replicate data from DB2 table. I have created MLOG$_REMOTE_TABLE1 on DB2 remote table.

I'm using Oracle 11g (p.s oracle 12g has no problem while executing this script )

While executing this script below , I'm getting Error:

CREATE MATERIALIZED VIEW REPL_TABLE1
REFRESH FORCE ON DEMAND
START WITH SYSDATE NEXT SYSDATE + 6/24
ENABLE QUERY REWRITE
AS
SELECT * FROM REMOTE_TABLE1_SYN;

ORA-30372: fine grain access policy conflicts with materialized view

Could you please, let me know why I have this Problem on 11g and not on the 12g (is it a bug of 11g)? And how can I solve it?

Answer 1

I found a Solution By using

REFRESH FORCE ON DEMAND WITH ROWID USING TRUSTED CONSTRAINTS

In my case I have table with aprox. 100 record, it's a small table so I can use ROWID. But using ROWID on a Big table is not good idea, because it will search the row in whole table, and replication of table will take too long time.

Answer 2

All you need is: REFRESH FORCE ON DEMAND USING TRUSTED CONSTRAINTS.

That way your query can ignore restrictions imposed by Oracle VPD.