Weird bug with Oracle's shared pool management?

From: bmlam <hansmayer1962_at_hotmail.com>
Date: 25 May 2005 02:44:30 -0700
Message-ID: <1117014270.186623.145500@g43g2000cwa.googlegroups.com>

This is a phenomenal bug in Oracle 9i:

SQL> select version from v$instance;

VERSION

---

9.2.0.4.0

What happened is I created a private synonym PROJST which initially pointed to a wrong view in another schema and the privs were not yet granted.

SQL> select * from projst where proj_id='GEHEIM'; select * from projst where proj_id='GEHEIM'

*

ERROR at line 1:
ORA-00942: table or view does not exist

That is not surprising. I then dropped that view, created another view and recreated the private synonym to point to the correct view. The privs were granted directly to the connecting user.

But after these actions:

SQL> select * from projst where proj_id='GEHEIM'; select * from projst where proj_id='GEHEIM'

*

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from projst where proj_id='GEHEIM!!!';

no rows selected

SQL> select * from projst where "PROJ_ID" ='GEHEIM';

no rows selected

It looks like Oracle has somehow pinned the first SQL statement in the shared pool with some internal reprsentation that is no longer valid after the drop and all subsequent queries which map to the same hash value will be still using that representation.

I just needed to change the query slightly e.g. by quoting the column name, Oracle starts with a new hash value and the query works.

I guess the problem can be solved with rebouncing the database, but since I have a workaround I will not bother.

I do not have the account to open a tar at Metalink so I hope some folks from Oracle Corporation can try to reproduce this problem and fix the bug eventually. Received on Wed May 25 2005 - 04:44:30 CDT