bmlam wrote:
>
> 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.
the next thing would be to reproduce under 9206
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Received on Wed May 25 2005 - 09:03:18 CDT
