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

Answer 1

I don't think there is a magic bullet here. It may be a missing table, or a misspelled table name in the query. It may be a privilege issue. You can't really tell without executing the query

I suggest you go ahead and instrument your code in such a way that you can turn it on and off. Run it, extract the query, and ship it off to your DBA to resolve.

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://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm

for some idea (which I just quickly googled for)

Answer 3

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.