This content has been marked as final. Show 3 replies
In general, there should be no issue.
You should test your application in a test instance with the parameter set to 10.2.0.2 and verify there are no issues before changing it in production.
If you are going to upgrade, why not upgrade to 11gR2 (the latest version) ? 10gR2 is already de-supported.
+"You queries will just be using 9i optimizer and plans. "+
COMPATIBLE means you won't use any feature that would prevent a downgrade, it has an influence on how Oracle stores the data.
Using the new features of the optimizer does not prevent a downgrade hence this aspect is not covered by the COMPATIBLE parameter (it is covered by OPTIMIZER_FEATURES_ENABLE)
Explanations by Tom Kyte : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:485421569569