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

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.

Asked by: Edgar977 | Posted: 21-01-2022

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.

Answered by: Lenny696 | Posted: 22-02-2022

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)

Answered by: Dainton686 | Posted: 22-02-2022

Answer 3

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

Answered by: Briony286 | Posted: 22-02-2022

Answer 4

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.

Answered by: Adrian914 | Posted: 22-02-2022

Answer 5

Please check of the tablespace name is correct if you are facing this issue while importing DB.

Answered by: Tara105 | Posted: 22-02-2022

Similar questions

java - ORA-00942: Table or view does not exist

I am passing a select query from java, it works for all table but one of the table gets an error "ora-00942 table or view does not exist". I checked grant and all, its same for all table. But for some reason it complains abtout this particular table. But if I run the same query in sql plus it works completely fine. Can someone tell me what may be the reason behind it. Thank you

java - Getting an exception ORA-00942: table or view does not exist - when inserting into an existing table

I am getting below exception, when trying to insert a batch of rows to an existing table ORA-00942: table or view does not exist I can confirm that the table exists in db and I can insert data to that table using oracle sql developer. But when I try to insert rows using preparedstatement in java, its throwing table does not exist error. Please find the stack trace of e...

java - exception ORA-00942: table or view does not exist - when inserting into an existing table using hibernate session

Am getting the following error when inserting a BLOB object to an Oracle Database table. It does not happen to all BLOB objects. While inserting a huge number of objects, most of them gets inserted before the error is thrown. So I can guarantee that the table does exist. We are inserting data by plainly saving the hibernate entity. No Prepared Statements are used. I have found this

java - ORA-00942: table or view does not exist error on writing to a table in database

I am trying to read a data from one database table using java + jdbc and trying to insert into another database table on different server in same session. I have created 2 connection object(con,conn1), each pointing to correct database. With 1st con object i am able to read the data but When it is going to write the data to another table using conn1 it is failing with error ORA-00942- table or view doesnt exis...

oracle - Getting ORA-00942: table or view does not exist using Blob in Java

I'm having problems when trying to insert in my ORACLE ddbb using PreparedStatement. Its seems to be related to the Blob data type than I'm handling. In this link changing type the solution was to change the type of the data from Blob to String but this is not what I'm looking for. I need ...

java - ORA-00942: table or view does not exist - oracle 11g exception

I am trying a oracle 11g connection with eclipse and my program lists the contents of a table, The code that i wrote for that is try { String connection ="jdbc:oracle:thin:@localhost:1521:xe"; String user ="testuser"; String password = "12345"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection(connection,user,password); stmt = con.create...

java - ORA-00942: table or view does not exist even though it does

I get this error when I try reading from an oracle database: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist However, I've tested the query with same credentials on a client and it works. What could be wrong. Find below my connection setup: @Bean(name="eJDBCDatasource") @ConfigurationProperties(prefix = "spring.datasourceexample") public Da...

oracle - getting error "ORA-00942: table or view does not exist " in java

I'm trying to learn connecting to oracle database when I log into sqlplus this way: sqlplus sys/Oracle_1@orcl as sysdba; I run this code: connect hr/hr@orclpdb select count(*) from employees; and I get this output: COUNT(*) ---------- 107 but when I'm trying to access this table in my java code I...

Still can't find your answer? Check out these amazing Java communities for help...

top