LOG MINER

Hi Friends.

I am trying to explore into log miner concepts.

My database is 8.1.7 running on windows 2000.

1.Both the packages DBMS_LOGMNR & DBMS_LOGMNR_D WERE INSTALLED from SYS.

Grant execute on dbms_logmnr to system;
grant execute on dbms_logmnr_d to system;

2.UTL_FILE_DIR WAS SET IN init.ora

3. Stopped and restarted the services.

4. logged in as system/*****

5. when i am trying to create a dictionary like

begin
execute DBMS_LOGMNR_D.BUILD(
dictionary_filename => 'orcldict.ora',
dictionary_location => 'c:\tmp');
end;

I am getting an error message :

execute DBMS_LOGMNR_D.BUILD(
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DBMS_LOGMNR_D" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DBMS_LOGMNR_D" to continue.

Any Help please!!!

Thanks
Ravi

Quote:

---

Originally posted by cravi

begin
execute DBMS_LOGMNR_D.BUILD(
dictionary_filename => 'orcldict.ora',
dictionary_location => 'c:\tmp');
end;

I am getting an error message :

execute DBMS_LOGMNR_D.BUILD(
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DBMS_LOGMNR_D" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DBMS_LOGMNR_D" to continue.

---

execute (in the way you are trying to use it) is a sqlplus command. Try:

Code:

---

begin
  DBMS_LOGMNR_D.BUILD(
      dictionary_filename => 'orcldict.ora',
      dictionary_location => 'c:\tmp');
end;

---

When i tried like this from system/****:

SQL> begin
2 DBMS_LOGMNR_D.BUILD(
3 dictionary_filename => 'orcldict.ora',
4 dictionary_location => 'c:\tmp');
5 end;
6 /
DBMS_LOGMNR_D.BUILD(
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00201: identifier 'DBMS_LOGMNR_D.BUILD' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

Also when i am trying from SVRMGR:

SVRMGR> execute dbms_logmnr_d.build('orcldict.ora', 'c:/tmp');
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 2

Thanks Much
Ravi

Quote:

---

Originally posted by cravi
When i tried like this from system/****:

SQL> begin
2 DBMS_LOGMNR_D.BUILD(
3 dictionary_filename => 'orcldict.ora',
4 dictionary_location => 'c:\tmp');
5 end;
6 /
DBMS_LOGMNR_D.BUILD(
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00201: identifier 'DBMS_LOGMNR_D.BUILD' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

---

This is telling you that DBMS_LOGMNR_D is unknown to you inside PL/SQL. Can you DESCRIBE it from sqlplus?
SQL> DESCRIBE DBMS_LOGMNR_D

If not, you don't have the correct permission on it.

HI

SQL> CONNECT SYSTEM/MANAGER;
Connected.
SQL> DESC DBMS_LOGMNR_D
ERROR:
ORA-04043: object DBMS_LOGMNR_D does not exist

The oracle doc said we have to install the package dbms_logmnr_d from SYS and grant execute permissions to SYSTEM and create the dictionary file from system. Thats what i did so far.

Can you give me any idea how to proceed from here please.

Thanks Much
Ravi

Can you see sys.DBMS_LOGMNR_D? If so, you need to setup a public synonym for sys.DBMS_LOGMNR_D...

Hi Jeff

SQL> connect sys/manager2
Connected.
SQL> desc dbms_logmnr_d
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN
DICTIONARY_LOCATION VARCHAR2 IN

WHAT I HAVE TO DO NOW.

Ravi

OK, so the package is definitely there.

First, connect as sys and create a public synonym.
connect sys/sys_password@db
create public synonym DBMS_LOGMNR_D for sys.DBMS_LOGMNR_D;


Then, grant execute privs to system:
grant execute on sys.DBMS_LOGMNR_D to system;

Next, connect as system and try to see the package:
connect system/system_password@db
describe DBMS_LOGMNR_D
describe sys.DBMS_LOGMNR_D

If both of these succeed, you should be able to execute the function you were looking for...

Hi Jeff

Thank you very much for your help and time. I followed the steps as you adviced but I landed into some other problem i.e.,

SQL> create public synonym DBMS_LOGMNR_D for sys.DBMS_LOGMNR_D;

Synonym created.

SQL> create public synonym DBMS_LOGMNR for sys.DBMS_LOGMNR_D;

Synonym created.

SQL> grant execute on sys.DBMS_LOGMNR_D to system;

Grant succeeded.

SQL> grant execute on sys.DBMS_LOGMNR to system;

Grant succeeded.

SQL> connect system/manager;
Connected.
SQL> describe DBMS_LOGMNR_D
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN
DICTIONARY_LOCATION VARCHAR2 IN

SQL> describe DBMS_LOGMNR
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN
DICTIONARY_LOCATION VARCHAR2 IN

THE ERROR IS :

SQL> SHO USER
USER is "SYSTEM"
SQL> begin
2 DBMS_LOGMNR_D.BUILD(
3 dictionary_filename => 'orcldict.ora',
4 dictionary_location => 'c:\tmp');
5 end;
6 /
begin
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 2

ORA-06512: at str line num
Cause: This is usually the last of a message stack and indicates where a problem occurred in the PL/SQL code.

Action: Fix the problem causing the exception or write an exception handler for this condition. It may be necessary to contact the application or database administrator

ORA-06532: subscript outside of limit
Cause: A subscript was greater than the limit of a VARRAY or non-positive for a varray or nested table.

Action: Check the program logic and increase the varray limit if necessary.


I HAVE NO CLUE HOW TO FIXUP THIS PROBLE. CAN YOU HELP ME JEFF.

THANKS
RAVI

Its kind of a BUG !!

What you need to do is, edit the dbmslmd.sql script and change the variable declaration of 'col_desc_array' from varray(513) to varray(700). And then, execute this script to recreate the package.

More explanation on Metalink...

Hi Jeff & SVK

Thanks alot for your help and time friends. As you guys adviced i changed col_desc_array from 513 to 700 and the dictionary file was created successfully.

I am going to further explore into the log miner and will contact you for the same if i end up with any new problems.

Thanks much once again
Ravi

Hi

I am trying to declare the log files to be monitored by DBMS_LOGMNR. I am getting an error message like:

SQL> begin
2 DBMS_LOGMNR.ADD_LOGFILE(
3 logfilename => 'C:\programfiles\Ora81\oradata\test\archive\[TEST]1_496.ARC',
4 options => dbms_logmnr.NEW);
5 END;
6 /
options => dbms_logmnr.NEW);
*
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00302: component 'NEW' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL> begin
2 DBMS_LOGMNR.ADD_LOGFILE(
3 logfilename => 'C:\programfiles\Ora81\oradata\test\archive\[TEST]1_496.ARC',
4 options => dbms_logmnr.ADDFILE);
5 END;
6 /
options => dbms_logmnr.ADDFILE);
*
ERROR at line 4:
ORA-06550: line 4, column 24:
PLS-00302: component 'ADDFILE' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Can you correct me please!!

Thanks
Ravi

ORA-06550: line num, column num: str
Cause: A PL/SQL compilation error has occurred. The numbers given for line and column are the location in the PL/SQL block where the error occurred.

Action: Refer to the following PL/SQL messages for more information about the error.

can you stop repeating your post?

Hey Pando

Its a mistake. Sorry for the Inconvenience.