Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB?

Question

I'm trying to config user to run logMiner from scheduled job periodly.

my user called admin I've grant the admin user the following:

 grant select on v_$database to admin
 grant execute on DBMS_LOGMNR to admin
 grant select on v_$logmnr_contents to admin

but still, when I'm trying to select from v$logmnr_contents I get - ORA-01031 insufficient privileges.

for testing I'm running the following script:

create or replace procedure Test_LogMiner AS
first_scn NUMBER(8);
last_scn NUMBER(8);
log_count NUMBER(8); 
BEGIN
  Select current_scn into last_scn from V$DATABASE;
  --Select max(mined_scn) into first_scn from my_table;
  first_scn := last_scn-100;
  DBMS_LOGMNR.START_LOGMNR(startScn=>first_scn, endScn=>last_scn,
     options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE);
  WRITE_LOG('LOG_DIR', 'After START_LOGMNR');
  -- get error on this select - ORA-01031 insufficient privileges-- 
  select count(*) into log_count from v$logmnr_contents;
  WRITE_LOG('LOG_DIR', 'select count(*)='||log_count);
  DBMS_LOGMNR.END_LOGMNR();
  WRITE_LOG('LOG_DIR', 'After END_LOGMNR');
EXCEPTION WHEN OTHERS THEN
  WRITE_LOG('LOG_DIR', 'Error: '||SQLERRM);
END; 

I can ran the same scenario, mean start logMiner and select from v$logmnr_contents, when it not run as procedure, from SQL developer as admin user (means: the create proc. removed and the rest code put in another begin / end)

It also runs when I run the proc from 'sys as dba' user.

My question is: What else should I grant in manner the procedure work?

Answer 1

You need EXECUTE_CATALOG_ROLE to be assigned to the user.

You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.

For details:Using LogMiner to Analyze Redo Log Files