Re: Dbms_stats giving OA-06512 error

On 01/05/2007 02:12:50 PM, William Wagman wrote:
> Greetings,
>
> I have been running dbms_stats.gather_schema_stats against
> test/development database for some time and recently added
>
> estimate_percent => dbms_stats.auto_sample_size
>
> Since which time ORA-06152 errora are generated...
>
> BEGIN dbms_stats.gather_schema_stats( ownname => 'EDRS',
> estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all
> columns', cascade => true, options => 'GATHER', degree => 15); END;
>
> *
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
> ORA-06512: at "SYS.DBMS_STATS", line 10502
> ORA-06512: at "SYS.DBMS_STATS", line 10996
> ORA-06512: at "SYS.DBMS_STATS", line 11183
> ORA-06512: at "SYS.DBMS_STATS", line 11237
> ORA-06512: at "SYS.DBMS_STATS", line 11214
> ORA-06512: at line 1

First of all, your method_opt is erroneous. You should specify something like 'FOR ALL COLUMNS SIZE SKEWONLY' or 'FOR ALL COLUMNS SIZE AUTO' or Wolfgang's favorite option 'FOR ALL INDEXED COLUMNS SIZE <histogram size>'. Second, the basic error is "ORA-00979 Not a group by expression", not 6512. You can catch the SQL statement throwing the error by issuing the following commands:

ALTER SYSTEM SET TRACEFILE_IDENTIFIER=STATS; ALTER SYSTEM SET EVENTS='979 TRACE NAME ERRORSTACK FOREVER, LEVEL 12'; That will catch the statement causing ORA-00979. If that doesn't help, you have to deal with a bug and should contact Oracle support. You didn't specify the version and the platform, which would expose you the the danger of sybranding in some other forums, but if you can, I advise you to upgrade to the highest patch level for your version.

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l