DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] [ID 1233745.1]

DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] [ID 1233745.1]
     
수정 날짜 16-SEP-2011     유형 PROBLEM     상태 PUBLISHED      

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 10.2.0.5 - Release: 9.2 to 10.2
Information in this document applies to any platform.
Symptoms
The symptoms are ORA-600[16515] when trying to delete statistics.


ORA-600 message:
ORA-00600: internal error code, arguments: [16515], [D], [2], [310302], [17], [1], [], []

The failing query:
begin
dbms_stats.delete_table_stats ( ownname => user, tabname => '<TABLE_NAME>', cascade_parts => true, cascade_columns => true, cascade_indexes => true, force => true);
end;
/

The call stack:
ksedst ksedmp ksfdmp kgeriv kgesiv ksesic5 kqdhsu kqrcmt ktcrcm kdapscs spefcmpa
spefmccallstd pextproc peftrusted psdexsp rpiswu2 psdextp pefccal pefcal

Cause

This is the same issue as found in Bug:4244360:
Abstract: ORA-600[16515] DURING DBMS_STATS.GATHER_TABLE_STATS AFTER SPLIT PARTITION

The error is due to a duplicate entry in the data dictionary.


To check for duplicate entries :
SQL> SELECT obj#,col#,intcol#,count(*) FROM hist_head$ GROUP BY obj#,col#,intcol# HAVING COUNT(*) >1 ORDER BY obj#;

Solution
The internal error is:
ORA-00600: internal error code, arguments: [16515], [D], [2], [310302], [17], [1], [], []
   The value 310302 is the object number (OBJ#)
   The value 17 is the column number (INTCOL#)


1) Check that problem can be reproduced in a clone database:
begin
dbms_stats.delete_table_stats ( ownname => user, tabname => '<TABLE_NAME>', cascade_parts => true, cascade_columns => true, cascade_indexes => true, force => true);
end;
/

--> ORA-600 reproduces

2) Find the rowid of the 2 rows:
SQL> CONNECT / AS SYSDBA
SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=310302 AND intcol#=17;


ROWID                    OBJ#    INTCOL# TIMESTAMP
------------------ ---------- ---------- ---------
AAAAD/AABAAAIIbABR     310302         17 16-JUL-10
AAAAD/AABAAAJU1AAC     310302         17 17-JUL-10

3) Delete the row having the oldest timestamp (eg 16-Jul-2010)
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> DELETE FROM hist_head$ WHERE ROWID='AAAAD/AABAAAIIbABR';
1 row deleted.

*** Ensure only 1 row is deleted ***

SQL> COMMIT;



4) Check if problem is solved:
begin
dbms_stats.delete_table_stats ( ownname => user, tabname => '<TABLE_NAME>', cascade_parts => true, cascade_columns => true, cascade_indexes => true, force => true);
end;
/

--> dbms_stats is successful

References
BUG:4244360 - ORA-600[16515] DURING DBMS_STATS.GATHER_TABLE_STATS AFTER SPLIT PARTION

관련 정보 표시 관련 자료
제품

    Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

키워드
DATA DICTIONARY; DBMS_STATS; DBMS_STATS.DELETE_TABLE_STATS; DBMS_STATS.GATHER_TABLE_STATS
오류
ORA-600[16515]