Symptoms are: wrong results from SELECT AND data corruption for DML
Although this post is off-topic, I think it is worth sharing one of the nastiest Oracle bug I ever encountered (I am not one of the Oracle pioneer, but I have nonetheless seen a number of them in my life). This is documented in Note:392673.1, which says it is only appearing in 10.1, (for which the manifestations are very rare), but with a much higher chance of occurring in 10.2.
This problem can occur only if all of the following are true:
- The SQL statement refers to some object without using a fully qualified object name
- The unqualified object name resolves to a different underlying object for different users.
- Sessions in different schemas execute IDENTICAL SQL sentences
The note says that the problem is related to reload of aged out cursors, and indeed I experienced it because statistics were calculated before the above conditions were met.