在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误
Thu Mar 08 23:29:37 2012 Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc (incident=38681): ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], [] Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_38681/chf_smon_24137_i38681.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Non-fatal internal error happenned while SMON was doing flushing of monitored table stats. SMON encountered 1 out of maximum 100 non-fatal internal errors.
trace文件中信息
从这里可以看出是对sys.col_usage$表进行update操作导致该错误发生
Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
========= Dump for incident 38681 (ORA 600 [13013]) ========
*** 2012-03-08 23:29:37.400
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3c1kubcdjnppq) -----
update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag,2),0,0
,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), like_preds
= like_preds + decode(bitand(:flag,16),0,0,1), null_preds = null_preds + decode(bitand(:flag,32),0,0,1), timestamp = :time where obj# = :ob
jn and intcol# = :coln
MOS中关于ORA-600 [13013]描述
Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Decimal RDBA of block being updated (Typically same as {c})
Arg [f] Code
验证MOS中描述
SQL> select dbms_utility.data_block_address_file(4198427) rfile,
2 dbms_utility.data_block_address_block(4198427) blocks
3 from dual;
RFILE BLOCKS
---------- ----------
1 4123
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
2 FROM DBA_EXTENTS A
3 WHERE FILE_ID = &FILE_ID
4 AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old 3: WHERE FILE_ID = &FILE_ID
new 3: WHERE FILE_ID = 1
Enter value for block_id: 4123
old 4: AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new 4: AND 4123 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI
----- ------------ ---------- ---------- -----
SYS COL_USAGE$ TABLE SYSTEM
--和trace文件中异常表一致
SQL> select object_type,object_name from dba_objects where object_id=518;
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE COL_USAGE$
--也和trace文件中异常表一致
分析异常表
SQL> ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> select index_name,COLUMN_NAME,COLUMN_POSITION FROM DBA_IND_COLUMNS
2 WHERE TABLE_NAME='COL_USAGE$';
INDEX_NAME COLUMN_NAM COLUMN_POSITION
--------------- ---------- ---------------
I_COL_USAGE$ OBJ# 1
I_COL_USAGE$ INTCOL# 2
SQL> set autot trace exp
SQL> SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
2 FROM sys.COL_USAGE$ t1
3 MINUS
4 SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
5 FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null;
no rows selected
--无记录返回
Execution Plan
----------------------------------------------------------
Plan hash value: 399371572
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4262 | 76716 | 27 (71)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 4262 | 38358 | 9 (12)| 00:00:01 |
| 3 | TABLE ACCESS FULL| COL_USAGE$ | 4262 | 38358 | 8 (0)| 00:00:01 |
| 4 | SORT UNIQUE NOSORT| | 4262 | 38358 | 18 (6)| 00:00:01 |
|* 5 | INDEX FULL SCAN | I_COL_USAGE$ | 4262 | 38358 | 17 (0)| 00:00:01 |
------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)
SQL> SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
2 FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
3 MINUS
4 SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
5 FROM sys.COL_USAGE$ t1;
OBJ# INTCOL#
---------- ----------
4294951004 2
4294951004 3
4294951004 4
4294951004 26
4294951004 27
4294951037 4
4294951037 5
4294951037 6
4294951037 9
4294951037 10
4294951840 11
OBJ# INTCOL#
---------- ----------
4294951840 12
4294951906 4
4294952709 3
4294952867 4
4294952867 9
16 rows selected.
--证明index中的记录比表中多了16条
解决问题并验证
SQL> alter index sys.I_COL_USAGE$ rebuild online; Index altered. SQL> SELECT /*+ FULL(t1) */ OBJ#,INTCOL# FROM sys.COL_USAGE$ t1 2 3 MINUS 4 SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 5 FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null 6 ; no rows selected SQL> SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 2 FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null 3 MINUS 4 SELECT /*+ FULL(t1) */ OBJ#,INTCOL# 5 FROM sys.COL_USAGE$ t1; no rows selected
这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
官方关于ORA-600[13013]说明
