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.
Technically there should not be any issues. You queries will just be using 9i optimizer and plans. The right way is to test the application in test first. If you are leaving compatible parameter to 9 for a long time , than what is the benefit of upgrading the database to 10. Think about it.
+"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