Not as straightforward as it would appear: [exec] [java] java.sql.SQLException: ORA-00942: table or view does not exist
Date: Wed, 14 May 2008 09:41:55 -0700
Oracle 10.2.0.2 on Redhat 2.6.9-22.214.171.124.2 oracle-jdbc-10.2.0.2.0
Weblogic uses jrockit-R27.4.0-jdk1.5.0_12
Suddenly late last week, our developers started getting the above error on SELECT statements. Yes, all proper verifications have been done - database connection string is correct, the correct usernames and passwords are being used and the queries can still be executed in SQL*Plus and Toad. The developers each have their own objects. A database bounce makes this go away. For a few days. Then it starts happening again. There have been no changes on the database side. I've searched Google, Metalink, and Oracle-L and I've opened an SR. I can see evidence that other customers have experienced this but I haven't found where any of them resolved it.
There is anecdotal evidence that this has happened here before in the past before my time. The word "version" as in Flashback Query has popped up in conversations. It is suspected that an incorrectly parsed query ends up in the shared pool for whatever reason and, from that point on, any further executions of that query result in the error. I tried flushing the shared pool, but then a developer started getting permissions errors, so I had to go ahead and bounce the database.
Has anyone had this problem and, if so, was there a resolution?