ORA-30372 fine grain access policy conflicts with materialized view

sql oracle replication materialized
jiboOne picture jiboOne · Aug 4, 2017 · Viewed 9.2k times · Source

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

jiboOne picture jiboOne · Aug 4, 2017

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More