关键字：ORA-00979 ORA-06512
今天分析一个表，遇到一个ora-00979 错误。
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXX'
,TabName => 'II_INMAININFO'
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
Runtime error occurred: 979 (ORA-00979: 不是 GROUP BY 表达式
ORA-06512: 在"SYS.DBMS_STATS", line 10502
ORA-06512: 在"SYS.DBMS_STATS", line 10516
ORA-06512: 在line 2)
我使用的oracle版本如下：
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
5 rows selected.
执行如下：
ALTER SESSION SET TRACEFILE_IDENTIFIER=i979trace;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER session SET EVENTS='979 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';
从跟踪文件取出错误结果，如下：
ksedmp: internal or fatal error
ORA-00979: 不是 GROUP BY 表达式
Current SQL statement for this session:
SELECT /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt
FROM (
SELECT /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact dynamic_sampling(0) no_monitoring */ TRUNC("OPER_DTIME") val, count(*) cnt
FROM "xxxx"."II_INMAININFO" t
WHERE TRUNC("OPER_DTIME") is not null
group by TRUNC("OPER_DTIME"))
order by val
我在表上建立了TRUNC("OPER_DTIME")的函数索引。如果删除trunc,执行如下sql语句是可以成功的：
SELECT /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt
FROM (
SELECT /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact dynamic_sampling(0) no_monitoring */ "OPER_DTIME" val, count(*) cnt
FROM "xxxx"."II_INMAININFO" t
WHERE "OPER_DTIME" is not null
group by "OPER_DTIME")
order by val
我在8i下执行以上sql语句是可以的，看来这个又是9i下的BUG。
