Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

1- Introduction:
After migration my Oracle version 10gR2 to 11gR2 (11.2.0.1), 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, ’11.1.0.7′ for example.

Thank you very much,
Nice reading

Wissem EL KHLIFI
www.oracle-class.com