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
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?