dbms_Logmnr_d Error
Sikandar Hayat, July 23, 2003 - 11:52 pm UTC
Hi TOM,
In Oracle 9.2 I am trying to use the logminer but getting the following error,
SQL> execute dbms_logmnr_d.build('dict.ora','d:\ora9i',options=>dbms_logmnr.store_in_flat_file);
BEGIN dbms_logmnr_d.build('dict.ora','d:\ora9i',options=>dbms_logmnr.store_in_flat_file); END;
*
ERROR at line 1:
ORA-06550: line 1, column 58:
PLS-00201: identifier 'DBMS_LOGMNR.STORE_IN_FLAT_FILE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
There was a bug in Oracle 8i but it is fixed in 9i so it seems some other issue. I have also set utl_file_dir.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/logminer.htm#18448
July 24, 2003 - 8:40 am UTC
you missed an _D
SQL> EXECUTE DBMS_LOGMNR<b>_D</b>.BUILD('dictionary.ora', -
2 '/oracle/database/', -
3 OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
Still same error
Sikandar Hayat, July 24, 2003 - 11:45 pm UTC
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', 'd:\ora9i', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
BEGIN DBMS_LOGMNR_D.BUILD('dictionary.ora', 'd:\ora9i', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); END;
*
ERROR at line 1:
ORA-06550: line 1, column 68:
PLS-00201: identifier 'DBMS_LOGMNR_D.STORE_IN_FLAT_FILE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> desc dbms_logmnr_d
ERROR:
ORA-04043: object dbms_logmnr_d does not exist
It seems that Oracle 9i has not create this package as default so I have to create it manual as in 8i?
July 25, 2003 - 7:06 am UTC
if it doesn't exist, it doesn't exist, install it.
My Mistake
Sikandar Hayat, July 28, 2003 - 12:15 am UTC
I granted dbs to scott and was using scott to create the dictionary but the package is in the SYS schema.
In the scott schema I have to give sys.dbms_logmnr_d.build instead of dbms_logmnr_d.build
execute sys.dbms_logmnr_d.build(options=>sys.dbms_logmnr_d.store_in_redo_logs);
This is just to share my issue with others.
Running logminer from a procedure
Alex Wong, December 15, 2005 - 12:47 am UTC
We want to run Logminer from a procedure so we can't use roles.
We seem to have a privileges problem:
Grant Execute On dbms_logmnr_d To alex1;
Grant succeeded
Grant Execute On dbms_logmnr To alex1;
Grant succeeded
Grant Select On v$logmnr_contents To alex1;
ORA-02030: can only select from fixed tables/views
Grant Select On v_$logmnr_contents To alex1;
Grant succeeded
----------------------
Then in Alex1 we do:
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD HH24:MI:SS';
Session altered
exec SYS.DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2005-12-14 00:00:', ENDTIME => Sysdate, OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE+SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY);
PL/SQL procedure successfully completed
Select SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'ALEX.A_ORDER.ORDER_ID'),SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'ALEX.A_ORDER.ORDER_NUMBER') From sys.V$LOGMNR_CONTENTS ;
Select SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'ALEX.A_ORDER.ORDER_ID'),SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'ALEX.A_ORDER.ORDER_NUMBER') From sys.V$LOGMNR_CONTENTS
ORA-00942: table or view does not exist
---------------------------------------------------------
What are we doing wrong?
December 15, 2005 - 10:30 am UTC
v$logmnr_contents is a public synonym. sys.public_synonym isn't right.
v$logmnr_contents would work
sys.v_$logmnr_contents would work.