0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.