Sunday, July 27, 2008

Funny “ORA-00942: table or view does not exist” Error Message

Dear all,

We all know when “ORA-00942” error message is returned. For instance, if I try to query a non-existent table then Oracle joyfully returns this errors message.

  
SQL> select * from funny;
select * from funny
              *
ERROR at line 1:
ORA-00942: table or view does not exist

“ORA-00942” error message is thrown on our face whenever we try to perform any action like CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ... against a non-existent table. But this error is sometimes misleading, like the one below:

  
SQL> create table t(a number);

Table created.

SQL> create view v as select * from t;

View created.

SQL> drop table v;
drop table v
           *
ERROR at line 1:
ORA-00942: table or view does not exist

Error message reports “table or view does not exist”, but definitely a view named “V” exists in the same schema.

Wouldn’t it be more appropriate if Oracle reports “table does not exist”?

2 comments:

Doll said...

I have report I am analyzing. Its an oracle standard report. If I check the log file for this report , it shows me the ORA-00942 error in the beginning but successfully completes the report. But this report takes a long time to run around 180 minutes to complete. DO you think this error may be causing it to delay. SHould be grant any connect or select access to the views/tables will help reducing the time to run this report.

Any help is appreciated.
Thanks

Asif Momen said...

Hi,

ORA-00942 will not cause your report to long longer than normal. You need to figure out SQL's which are performing bad and tune them.

Regards