Tracing ORA errors

Hi Everyone,

Today one of the developer came and asked me to find the sql which is causing the ORA error in his custom application. He was accessing the URL and he was getting 'ORA-00942' error in the screen.

I have gone ahead and enabled system wide event the error ORA-00942.
This statement will capture the 942 errors.

SQL> alter system set events '942 trace name errorstack level 1';

System altered.


Asked the developer to reproduce the issue.
In the trace directory, traces were generated.

mfgdev2 $ ls -ltr |tail -4
-rw-r----- 1 oracle dba 270402 May 6 23:34 mfgdev2_ora_166444.trc
-rw-r--r-- 1 oracle dba 573 May 6 23:35 alert_mfgdev2.log
-rw-r----- 1 oracle dba 8690 May 6 23:35 mfgdev2_ora_166491.trm
-rw-r----- 1 oracle dba 269607 May 6 23:35 mfgdev2_ora_166491.trc
mfgdev2 $

Opening the last trace file gave the sql that was causing the ORA-00942 error.!!

*** ACTION NAME:() 2010-05-06 23:35:55.859

----- Error Stack Dump -----

*** 2010-05-06 23:35:55.888
----- Current SQL Statement for this session (sql_id=2bna76zq9pgzp) -----

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+88 CALL skdstdst() 00000000A ? 000000001 ?

Do not forget to disable the event.
I have disabled the event after capturing the SQL which was causing the error message by

SQL> ALTER SYSTEM SET EVENTS '942 trace name errorstack off';

System altered.


Hope this will help you in similar situations. Happy Sharing. !!!
As usual leave your comments.


Santosh said...

Hi Suresh,

Santosh here. Can we use this fo every error we see in db or alert log or is it for specific errors ???



Anonymous said...

If you want to lookup ORA messages on your Android smartphone, try to use this application:

Suresh Lakshmanan said...

Hi Santosh,

Most of the errors fall under this category.