Bug 5458753 SQL may execute in wrong schema

I found this issue this afternoon....
Waaw... completely crazy!!!!


This issue is very very rare in 10.1 but can occur
much more easily in 10.2.0.2 .

If different schemas have tables / views etc.. with identical names
and sessions from different schemas execute IDENTICAL SQL
statements then it is possible for such SQL sentences to get
executed against the wrong schema objects. This can result
in:

• Wrong results from SELECT SQL
• Logical data corruption for DML SQL
• Unexpected errors such as ORA-2291 / ORA-1 from DML
• Parse errors if the underlying table definition differs.

(eg: ORA-904 etc..)

eg:
Consider an application with seperate sessions executing
"select mycol from mytable where key=:B1" as user A and user B
where both users have a table called "MYTABLE".
In this case it is possible for user A to end up executing the
SQL against B.MYTABLE instead of against A.MYTABLE.

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.



More info in Metalink note: 392673.1