收集统计信息报ORA-00600 16515问题解决

在一套win 11.2.0.1的数据库在收集统计信息报ORA-600 16515错误

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1


SQL> exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL');
BEGIN dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true,granularity=>'ALL'); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 25335
ORA-06512: ?? "SYS.DBMS_STATS", line 25511
ORA-06512: ?? "SYS.DBMS_STATS", line 25467
ORA-06512: ?? line 1

确认报错对象

SQL> select owner, object_name, object_type
  2  from sys.dba_objects
  3  where object_id =6375;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SYS
WRI$_SQLMON_USAGE
TABLE

对对象进行处理,确认对象本身没有问题,尝试收集统计信息依旧报错

SQL> alter table WRI$_SQLMON_USAGE move;

表已更改。

SQL> select index_name from dba_indexes where table_name='WRI$_SQLMON_USAGE';

未选定行

SQL> SELECT COUNT(1) FROM WRI$_SQLMON_USAGE;

  COUNT(1)
----------
         1

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

删除统计再尝试

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'WRI$_SQLMON_USAGE');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();
BEGIN dbms_stats.gather_database_stats_job_proc(); END;

*
第 1 行出现错误:
ORA-00600: ????????????, ????: [16515], [U], [40], [6376], [1], [0], [], [],
[], [], [], []
ORA-06512: ?? "SYS.DBMS_STATS", line 31228
ORA-06512: ?? line 1

使用ANALYZE收集统计信息正常

SQL> ANALYZE TABLE  WRI$_SQLMON_USAGE  COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

表已分析。

删除hist_head$中对应报错行记录,ORA-00600 16515问题解决

SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=6376 AND intcol#=1;

ROWID                    OBJ#    INTCOL# TIMESTAMP#
------------------ ---------- ---------- --------------
AAAAHAAABAAAIELABQ       6376          1 01-4?  -24

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

SQL>  DELETE FROM hist_head$ WHERE ROWID='AAAAHAAABAAAIELABQ';

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> begin
  2  dbms_stats.delete_table_stats ( ownname => user, tabname => 'WRI$_SQLMON_USAGE', cascade_parts => true, cascade_col
umns => true, cascade_indexes => true, force => true);
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_database_stats_job_proc();

PL/SQL 过程已成功完成。

参考文档:DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1)