Identical tables in schemas, data inserted into the wrong schema

[NetComrade]

We have tables into schemas which are identical.
Occasionally we find that records were inserted or updated in the
wrong schema. What makes us think it's an oracle issue, is that the
transaction is in PL/SQL procedure, and there are no grants to insert
or even select between schemas. Can't rule out an application issue
completely yet, but app never connects to 2 schemas at the same time,
and what kind of points that a transaction occurs within pl/sql is
that the records that are inserted in the wrong schema and the right
schema as part of the same transaction have the same timestamp.

Before we start digging through redo logs with logminer, I wanted to
know if there are any known bugs on the issue. This is not consistent
or reproducible issue at this point. This would have to be a pretty
big bug.

version is 10g std
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
remove NSPAM to email

[Michel Cadot]

"NetComrade" <netcomr...@bookexchange.net> a écrit dans le message de news: era723tl561ldjtfb...@4ax.com...

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

Regards
Michel Cadot

[NetComrade]

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.