ORA-00942: table or view does not exist : How do I find which table or view it is talking about

Question

We're running a java/hibernate app going against ORACLE 10g in TESTING. Once in a while, we're seeing this error:

ORA-00942: table or view does not exist

Is there a way to find out which table/view(s) ORACLE is talking about ?

I know that I can add extra levels of logging in hibernate which will show all the SQL that it executes on ORACLE and then run that SQL to figure out which TABLE/VIEW is missing or missing permission. But given that it is in TESTING/STAGING, that will slow down performance.

Is there a simple way to narrow down on the Table/View Name ?

UPDATE :

Just so you know, I don't have control over the Oracle DB Server Environment.
I enabled Hibernate tracing/logging and found a VALID SQL. I even put Wireshark(which is a TCP packet filter) to see what hibernate actually sends and that was a valid SQL. So, why would Oracle complain about it once in a while and NOT always.

Answer 1

This is what I do, appologies to whoever this originally came from, I know I took it from some website, but can't remember where right now.

In preproduction, I have this

create table caught_errors (
  dt        date,               
  username  varchar2( 30), -- value from ora_login_user
  msg       varchar2(2000),
  stmt      varchar2(2000)
);


create or replace trigger catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into 
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;
/

Any time servererror is thrown, its caught and logged to a table, I can then check that table to find the offending queries, and refund them as needed to see the missing table (when you run the query in sqlplus, it will tell you the table)

Note, yes, there is issues with this, eg, what if caught_errors is dropped, or raises an error itself, you could get recursive loop, hence why this only exists in preproduction.

Answer 2

Take a look into the DBA_AUDIT_EXISTS table, when auditing is turned on for Oracle. I believe that Oracle can provide very detailed auditing which you can simply toggle on and off when you like via DB commands, although I dont remember what they are off the top of my head.

See: http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm

for some idea (which I just quickly googled for)

Answer 3

You should check the account, which whether has the permit to access the target table.