|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Revealing the offending sql statement for an ORA-00942
Here's an (admittedly esoteric) way to use diagnostic events to create a trace file when a certain error occurs.
A few tables are created:
create table table_one (a number, b number); create table table_two (a number, b number); create table table_three (a number, b number); create table table_four (a number, b number); create table table_five (a number, b number);
Some selects are issued against the tables.
set feedback off select * from table_one; select * from table_two; select * from table_htree; select * from table_four; select * from table_five;
The third select statement will obviously fail with a ORA-00942 because the table name was misspelled.
Now, let's prepend that these selects are issued within an application over whose source coude we don't have any control. Further, the only thing that the application reports, is the ORA-00942. We're left in the dark what table (or view) is actually the problem.
So, the following event is set which will write a trace file whenever an ORA-00942 occurs.
alter system set events '942 trace name errorstack level 1';
The selects are re-executed.
Now, there is an entry in the alert log:
Errors in file d:\oracle\product\10.1.0\db_1\rdbms\trace\ora10_ora_696.trc: ORA-00942: table or view does not exist
The entry comes with a file name. The beginning of the file reads:
Dump file d:\oracle\product\10.1.0\db_1\rdbms\trace\ora10_ora_696.trc Fri Mar 25 00:36:40 2005 ORACLE V10.1.0.2.0 - Production vsnsta=0 vsnsql=13 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options Windows XP Version V5.1 Service Pack 2 CPU : 1 - type 586 Process Affinity: 0x00000000 Memory (A/P) : PH:516M/1022M, PG:2058M/2459M, VA:1822M/2047M Instance name: ora10 Redo thread mounted by this instance: 1 Oracle process number: 10 Windows thread id: 696, image: ORACLE.EXE (SHAD) *** ACTION NAME:() 2005-03-25 00:36:40.609 *** MODULE NAME:(SQL*Plus) 2005-03-25 00:36:40.609 *** SERVICE NAME:(SYS$USERS) 2005-03-25 00:36:40.609 *** SESSION ID:(21.9) 2005-03-25 00:36:40.609 *** 2005-03-25 00:36:40.609 ksedmp: internal or fatal error ORA-00942: table or view does not exist Current SQL statement for this session: select * from table_htree ----- Call Stack Trace -----
Using a servererror trigger
Of course, it's probably easier to use a servererror trigger to find offending sql statements.