Home » SQL & PL/SQL » SQL & PL/SQL » Fine Grain Access Policy (Oracle, 12.2.0.1.0, Linux)
Fine Grain Access Policy [message #686564] Wed, 12 October 2022 08:07 Go to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
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.
Re: Fine Grain Access Policy [message #686568 is a reply to message #686564] Thu, 13 October 2022 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68336
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe out of topic (just a little bit) but why can't you do use Oracle scheduler?

[Updated on: Thu, 13 October 2022 08:31]

Report message to a moderator

Re: Fine Grain Access Policy [message #686572 is a reply to message #686568] Thu, 13 October 2022 20:01 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
I'm not real sure but that came from the Top down. At that point, you do as you are told.
Previous Topic: xml generate from db on redaction column
Next Topic: Dynamic Source Table & Target Table
Goto Forum:
  


Current Time: Thu Jan 26 19:59:12 CST 2023