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
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
Raj
----------------------------------------------
Got RAC?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
