I'm guessing I have an issue with the error of "ORA-30372: fine grain access policy conflicts with materialized view" when running a materialized view.
I'm trying to refresh a Context Index using a Materialize View since I can't run a DBMS_SCHEDULER job to refresh the index.
Function
CREATE OR REPLACE function DirectoryContextFN return varchar2 is
pragma autonomous_transaction;
begin
ctx_ddl.sync_index('search_field_mv_indx');
ctx_ddl.optimize_index('search_field_mv_indx', 'FULL');
return null;
end DirectoryContextFN;
Materialized View
CREATE MATERIALIZED VIEW DIRECTORY_CONTEXT_MV
(REFRESH_INDEX,REFRESH_DATE)
NOCACHE
NOLOGGING
NOCOMPRESS
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('12-10-2022 07:59:44','dd-mm-yyyy hh24:mi:ss')
NEXT sysdate + 1/24
WITH PRIMARY KEY
AS
(select DirectoryContextFN refresh_index, sysdate refresh_date from dual);
Error
SQL> BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => DIRECTORY_CONTEXT_MV'
,METHOD => 'C'
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,ATOMIC_REFRESH => TRUE
,NESTED => FALSE
,OUT_OF_PLACE => FALSE);
END;
>> BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => DIRECTORY_CONTEXT_MV'
,METHOD => 'C'
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,ATOMIC_REFRESH => TRUE
,NESTED => FALSE
,OUT_OF_PLACE => FALSE);
END;
Error at line 2
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2960
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2378
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexOptimize
ORA-30372: fine grain access policy conflicts with materialized view
ORA-06512: at " DIRECTORYCONTEXTFN", line 21
ORA-06512: at "CTXSYS.DRUE", line 171
ORA-06512: at "CTXSYS.CTX_DDL", line 1268
ORA-06512: at "DIRECTORYCONTEXTFN", line 14
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2360
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2916
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3199
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3229
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 2
Script stopped on line 2.
Any suggestions on fixing this or is it just not possible to accomplish what I'm trying to do. Missing a grant...etc. Or is it my only option is to run a DBMS_SCHEDULER job at a scheduled time to update the Index. Which is something I can't do.