Oracle Database Discussions

Hi all,

I work on an application that runs on Tomcat (or any web server really) and connects to an Oracle db. We experience an error that seemed to happen every once in a while, but lately seems to occur daily (in fact, it's only 11:35am and I have encountered the issue twice already this morning in two separate schemas). The problem is that Oracle returns a "table or view does not exist" error to our application even though the table does indeed exist. How do I know that? Because I can connect to the db using SQLDeveloper, Toad, SQLPlus, etc. and query the table, delete/insert/update, etc. But when our application attempts to query or modify the table, it receives the error about the table not existing and displays it to the user.

For a while we weren't sure how to fix it, until one day we found that if our program executed a query as a prepared statement then it would fail. If we executed a query without using a prepared statement and just hard-coded the SQL string to be run, then the same query would execute fine. Our only conclusion was that Oracle had to be caching information and the cache must have erroneous data in it. So we searched for a way to clear it out and stumbled upon the following two commands:

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;


You have to connect in as system for these to work properly, but once run the problem immediately clears up. Suddenly you are able to login again/view the page that you previously couldn't/whatever and all without the annoying error of the missing table or view.


Has anyone else ever experienced this issue? I haven't heard of any of our clients reporting this issue yet, but several bug reports have been opened internally that all relate to this issue and it's becoming so persistent that it is very annoying. Does anyone know why our application keeps being told that tables do not exist even though they do...and why does this only occur when issuing a prepared statement? I can only guess we must have some setting wrong in Oracle or something.

Thanks!