SQL may execute in wrong schema

  • From: rjamya <rjamya@xxxxxxxxx>
  • To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 20 Sep 2006 06:53:04 -0400

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_

 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

Got RAC?

Other related posts: