Oracle Compatible Parameter to set Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?
The parameter is described in the Upgrade Guide. You cannot downgrade a database to a version that is below the version of the COMPATIBLE parameter. The COMPATIBLE parameter controls how data is written to disk. But it does not control the usage of optimizer features. There are a lot of parameter to control the oprimizer features. The OPTIMIZER_FEATURES_ENABLED is a parameter that you have to set appropriately to enable the optimizer features of the Oracle version. Tom Kytes discusses the difference of these two parameter in this article.