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

"NetComrade" <netcomradeNSPAM_at_bookexchange.net> a écrit dans le message de news: era723tl561ldjtfb7ge6v2025h71m4tf9_at_4ax.com...
| 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

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 Received on Mon Apr 16 2007 - 13:53:55 CDT