Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL may execute in wrong schema

Re: SQL may execute in wrong schema

From: Nilo Segura <>
Date: Wed, 20 Sep 2006 13:36:08 +0200
Message-ID: <>

Yes, several of your applications were hit very badly by this bug. It causes severe logical data corruption as the
results of your SQL manipulations end up on different schemas, and there was no single indication of the problem (not ORA-600 etc ).

The bug was escalated to severity one in Oracle. Development produced an internal test patch that we tested one week ago if memory serves well (I'm on holidays right now). The patch did fix the problem according to our tests.

To clarify the problem: imagine that you have the same application that is deployed in several database accounts in the same database server. In the shared pool, you have cursors with the same SQL stmt (including table names etc). The only
difference is that the cursors are owned by different users..... However, under certain "special" conditions (heavy load etc), Oracle gets totally confused and can not tell one cursor from another.... So modifications done by user PETER ended up in the tables of user JOHN.

Nasty indeed....

There was an initial workaround ...

_kks_use_mutex_pin=false. (This setting disables new mutex functionality switched on in in is was set to false.

But apparently it had some other unforeseen consequences, that is why the only "safe" workaround was to fully qualify all the object names with the owner name in the application code... .to avoid the "confusion"....

As I said, this affected several CERN grid and physics experiments applications, this problem was "very visible" and generated a lot of "noise" ;)

Now we are waiting for the official patch to be published. The CPU July patch I think does not touch the fixed part of the code (so I think they are compatible, to be confirmed). I do not know if it will be added to the incoming (my guess is that it will not, it is too late), so another patch for will be needed.

And remember, this problem only happens in the conditions I described above, we have quite a number of installations and they run very well.

Nilo Segura
Oracle support - IT/DE
CERN - Geneva

On 9/20/06, rjamya <> wrote:
> This morning I found this bug which " can occur much more easily " in
> 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
> Raj
> ----------------------------------------------
> Got RAC?
> --

Nilo Segura
Oracle Support - IT/DES
CERN - Geneva

Received on Wed Sep 20 2006 - 06:36:08 CDT

Original text of this message