客户的告警日志中发现了这个ORA-600错误。
详细的错误信息为：
Wed Jan 12 22:17:15 2011 KCF: WRITE/OPEN error block=0x3f6689 online=1 file=2 /TEMP/B1EDB/temp02.dbf error=27072 txt: 'HPUX-ia64 Error: 28: No space left on device Additional information: 4 Additional information: 4155017 Additional information: -1' Automatic tempfile offline due TO WRITE error ON file 2: /TEMP/B1EDB/temp02.dbf Wed Jan 12 22:17:16 2011 Errors IN file /oracle/db/admin/B1EDB/bdump/b1edb_j001_13014.trc: ORA-00600: internal error code, arguments: [ksfdsyncdata2], [0xC000000335E58668], [], [], [], [], [], [] ORA-00372: file 302 cannot be modified at this TIME ORA-01110: DATA file 302: '/TEMP/B1EDB/temp02.dbf' ORA-00372: file 302 cannot be modified at this TIME ORA-01110: DATA file 302: '/TEMP/B1EDB/temp02.dbf' Wed Jan 12 22:17:18 2011 Errors IN file /oracle/db/admin/B1EDB/bdump/b1edb_j000_12908.trc: ORA-12012: error ON auto EXECUTE OF job 8952 ORA-00376: file ORA-00376: file 302 cannot be READ at this TIME ORA-01110: DATA file 302: '/TEMP/B1EDB/temp02.dbf' ORA-06512: at "SYS.PRVT_ADVISOR", line 1624 ORA-06512: at "SYS.DBMS_ADVISOR", line 186 ORA-06512: at "SYS.DBMS_SPACE", line 1500 ORA-06512: at "SYS.DBMS_SPACE", line 1566 cannot be READ at this TIME |
这个ORA-600错误本身比较罕见，在整个metalink也找不到说明，不过结合系统的错误信息，到是可以分析出系统的问题。
显然在ORA-600错误出现之前，出现了临时表空间不足的问题。Oracle对于临时表空间，并不会想普通表空间那样将整个数据文件进行初始化，而是在真正用到的时候再去分配。这里的问题是，临时文件所在操作系统目录上空间已经被用光，而当出现排序或连接需要分配临时段时，在写临时文件时，出现了错误。
/oracle/db/admin/B1EDB/bdump/b1edb_j001_13014.trc Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options ORACLE_HOME = /oracle/db System name: HP-UX Node name: EDB Release: B.11.23 Version: U Machine: ia64 Instance name: B1EDB Redo thread mounted BY this instance: 1 Oracle process NUMBER: 20 Unix process pid: 13014, image: oracle@EDB (J001) *** ACTION NAME:(GATHER_STATS_JOB) 2011-01-12 22:17:16.421 *** MODULE NAME:(DBMS_SCHEDULER) 2011-01-12 22:17:16.421 *** SERVICE NAME:(SYS$USERS) 2011-01-12 22:17:16.421 *** SESSION ID:(590.24179) 2011-01-12 22:17:16.421 *** 2011-01-12 22:17:16.421 ksedmp: internal OR fatal error ORA-00600: internal error code, arguments: [ksfdsyncdata2], [0xC000000335E58668], [], [], [], [], [], [] ORA-00372: file 302 cannot be modified at this TIME ORA-01110: DATA file 302: '/TEMP/B1EDB/temp02.dbf' ORA-00372: file 302 cannot be modified at this TIME ORA-01110: DATA file 302: '/TEMP/B1EDB/temp02.dbf' CURRENT SQL statement FOR this SESSION: SELECT i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,MOD(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,NULL,NULL,MOD(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread FROM ind$ i, ind_stats$ ist, (SELECT enabled, MIN(cols) unicols,MIN(to_number(bitand(defer,1))) deferrable#,MIN(to_number(bitand(defer,4))) valid# FROM cdef$ WHERE obj#=:1 AND enabled > 1 GROUP BY enabled) c WHERE i.obj#=c.enabled(+) AND i.obj# = ist.obj#(+) AND i.bo#=:1 ORDER BY i.obj# ----- PL/SQL Call Stack ----- object line object handle NUMBER name c00000033d7f01d8 519 package body SYS.DBMS_STATS_INTERNAL c00000033d7f01d8 531 package body SYS.DBMS_STATS_INTERNAL c00000033ee5e870 8533 package body SYS.DBMS_STATS c00000033ee5e870 8580 package body SYS.DBMS_STATS c00000033ee5e870 8672 package body SYS.DBMS_STATS c00000033ee5e870 8752 package body SYS.DBMS_STATS c00000033ee5e870 12415 package body SYS.DBMS_STATS c00000033ee5e870 12892 package body SYS.DBMS_STATS c00000033ee5e870 13345 package body SYS.DBMS_STATS c00000033ee5e870 19409 package body SYS.DBMS_STATS ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ksedst()+64 CALL ksedst1() 000000000 ? 000000001 ? ksedmp()+2176 CALL ksedst() 000000000 ? C000000000000C9F ? 400000000404CC40 ? 000000000 ? 000000000 ? 000000000 ? ksfdmp()+48 CALL ksedmp() 000000003 ? kgerinv()+304 CALL ksfdmp() C000000000000612 ? 000000003 ? 40000000094FF110 ? 00001830B ? 000000000 ? 000000000 ? kgesinv()+64 CALL kgerinv() 6000000000031370 ? 4000000001A4B3E0 ? 6000000000032428 ? 4000000001A4B3E0 ? 9FFFFFFFFFFC5700 ? ksesin()+176 CALL kgesinv() 6000000000031370 ? 60000000001E85A0 ? 60000000001E85B0 ? 60000000000327A0 ? 9FFFFFFFFFFC5700 ? $cold_ksfdsyncdata( CALL ksesin() 4000000000F09090 ? )+256 000000001 ? 60000000000C2790 ? 9FFFFFFFFFFC5700 ? 60000000000C2EC0 ? 60000000000B5E18 ? C000000000000286 ? C000000000000998 ? kcflsync()+160 CALL $cold_ksfdsyncdata( C0000003356BF050 ? ) C00000000000038C ? 4000000002549860 ? C000000335E586B8 ? C0000003356BF070 ? |
显然，这是JOB调用的DBMS_STATS收集统计信息的会话，从错误发生的时间上看，导致临时空间不足的多半也是收集统计信息的过程。显然这个ORA-600的错误，正是由于DBMS_STATS碰到了临时空间不足所导致的。
这个错误没有什么危害，只要避免空间不足的问题，就可以避免这个ORA-600的产生。