Re: Weird bug with Oracle's shared pool management?

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.

Just tried it and it worked just fine.

I suspect what you didn't remember to do, based on your description, was GRANT SELECT ON <view_name> when you recreated the view.

You did not grant privileges on the new view to the other schema and a synonym does not grant privileges.

HTH

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)