Re: Identical tables in schemas, data inserted into the wrong schema

On Mon, 16 Apr 2007 20:53:55 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>Yes there is a known bug.
>Here's a mail posted in oracle-l last fall:
>
> Subject: SQL may execute in wrong schema
>
>
>This morning I found this bug which " can occur much more easily " in
>10.2.0.2 onwards and the workaround is so funny.
>
>Essentially this bug happens when you have more than one schema with
>identical table/view names and two users are executing same SQL but in
>different schema. Sometimes the SQL will execute in the _wrong_
>schema.
>
>"Workaround:
> There is no simple workaround which can avoid this fully.
> The issue can be avoided by prefixing object names with the schema name.
> eg: In the above example change the SQL to use
> "select mycol from A.mytable" for user A and
> "select mycol from B.mytable" for user B.
>
> If SQL cannot be changed then it can help to reduce shared pool
> load (as the problem occurs when cursors are reloaded having
> been aged out / invalidated). Using DBMS_SHARED_POOL.KEEP for
> affected cursors can also help.:
>
>BUG# 5458753
I am amazed how they manage to screw something like that up, you'd think that 'code' has been stable for years :)

I am also ashamed I didn't catch it in this group, since I am sure it was discussed before.

Thanks.
.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email Received on Mon Apr 16 2007 - 16:01:50 CDT