After migration my Oracle version 10gR2 to 11gR2 (184.108.40.206), Some of the queries did not run successfully.
Some of the queries gave me an ORA-979 error.
In this article, I am going to explain how to fix the error. Oracle says ORA-979 in 11gR2 is a bug.
The bug number is 9411496.
2- Environment test details:
First, I am going to create a test table, then insert a few records and run a query with a group by expression.
The query gives an error, ORA-979: not a GROUP BY expression
3- Solution 1:
You can Set the hidden parameter “_FIX_CONTROL”=’5520732:OFF’ in the pfile. This parameter disables a given bug number.
_fix_control is an undocumented parameter that can be used to turn off/on a particular bug fix identifier. This is a solution introduced from the version 10.2.0.2 of Oracle. (Be careful when using this parameter, it can prevent your database from starting).
We create a pfile from the spfile. Then we edit the pfile and we set the _FIX_CONTROL”=’5520732:OFF’. After shut down the instance, we create the spfile from the pfile and finally, we start up it.
Now, We run the query and it successfully gives a result.
3- Solution 2:
First, let’s remove the parameter _FIX_CONTROL”=’5520732:OFF’ from the pfile.
After running again the query, we get the ORA-979 error.
The solution 2 is to set the parameter optimizer_features_enable to a previous version of Oracle, ’220.127.116.11′ for example.
Thank you very much,
Wissem EL KHLIFI