Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

How to fix ORA-979: not a GROUP BY expression in 11.2.0.1 version

The same article is published in my Website : www.oracle-class.com

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
http://www.oracle-class.com

1 Comment »

  1. Nice knowledge! I have been looking for some thing like that for a while finally. With thanks!

    Comment by Marinda — February 1, 2011 @ 5:54 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: