Christian Bilien’s Oracle performance and tuning blog

March 14, 2007

SQL can execute in wrong schema (10.2)

Filed under: Off topic,Oracle — christianbilien @ 4:54 pm

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.

Advertisements

Blog at WordPress.com.