测试环境的新建表空间在删除时报错。
数据库创建一个新的表空间后执行删除，出现了ORA-600的错误信息：
SQL> SELECT tablespace_name FROM dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS SYSAUX TEMP TBS_1 GGX USERS PERFSTAT STATSPACK ALA_TEST 10 ROWS selected. SQL> DROP tablespace ala_test including contents AND datafiles; DROP tablespace ala_test including contents AND datafiles * ERROR at line 1: ORA-00600: internal error code, arguments: [ktssdrp1], [9], [13], [11], [], [], [], [] 错误信息为： Tue Mar 13 18:11:14 CST 2012 DROP tablespace ala_test including contents AND datafiles Tue Mar 13 18:11:14 CST 2012 Errors IN file /u01/app/oracle/admin/orcl10g/udump/orcl10g_ora_7838.trc: ORA-00600: internal error code, arguments: [ktssdrp1], [9], [13], [11], [], [], [], [] Tue Mar 13 18:11:18 CST 2012 ORA-600 signalled during: DROP tablespace ala_test including contents AND datafiles... |
详细信息为：
Dump file /u01/app/oracle/admin/orcl10g/udump/orcl10g_ora_7838.trc Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: hpserver2.enmotech.com Release: 2.6.32-100.28.5.el6.x86_64 Version: #1 SMP Wed Feb 2 18:40:23 EST 2011 Machine: x86_64 Instance name: orcl10g Redo thread mounted BY this instance: 1 Oracle process NUMBER: 13 Unix process pid: 7838, image: oracle@hpserver2.enmotech.com (TNS V1-V3) *** ACTION NAME:() 2012-03-13 18:11:14.802 *** MODULE NAME:(sqlplus@hpserver2.enmotech.com (TNS V1-V3)) 2012-03-13 18:11:14.802 *** SERVICE NAME:(SYS$USERS) 2012-03-13 18:11:14.802 *** SESSION ID:(30.124) 2012-03-13 18:11:14.802 *** 2012-03-13 18:11:14.802 ksedmp: internal OR fatal error ORA-00600: internal error code, arguments: [ktssdrp1], [9], [13], [11], [], [], [], [] CURRENT SQL statement FOR this SESSION: DROP TABLE "APP"."SERVICE" cascade constraints purge ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ssd_unwind_bp: unhandled instruction at 0x3d12d3e instr=f ssd_unwind_bp: unhandled instruction at 0x1344b61 instr=f ksedst()+31 CALL ksedst1() 000000000 ? 000000001 ? 7FFFA35C05C0 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? ksedmp()+610 CALL ksedst() 000000000 ? 000000001 ? 7FFFA35C05C0 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? ksfdmp()+63 CALL ksedmp() 000000003 ? 000000001 ? 7FFFA35C05C0 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? kgerinv()+161 CALL ksfdmp() 006AE9A40 ? 000000003 ? 7FFFA35C05C0 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? kgeasnmierr()+163 CALL kgerinv() 006AE9A40 ? 008754D28 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? 000000000 ? ktssdrp_segment()+2 CALL kgeasnmierr() 006AE9A40 ? 008754D28 ? 289 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? 000000009 ? dtbdrp()+1736 CALL ktssdrp_segment() 7FFFA35C2998 ? 008754D28 ? 7FFFA35C0620 ? 7FFFA35C0560 ? 000000000 ? 000000009 ? dtbdrv()+2732 CALL dtbdrp() 7FFFA35C2998 ? 0779C1148 ? 074FB4318 ? 7FFFA35C2CB0 ? 000000000 ? 7FFFA35C2CE8 ? opiexe()+13175 CALL dtbdrv() 7FFFA35C2998 ? 0779C1148 ? 000000000 ? 7FFFA35C2CB0 ? 000000000 ? 7FFFA35C2CE8 ? opiosq0()+3398 CALL opiexe() 000000004 ? 000000000 ? 7FFFA35C3F2C ? 000000005 ? 000000000 ? 7FFFA35C2CE8 ? opiosq()+14 CALL opiosq0() 000000003 ? 00000000F ? 7FFFA35C5600 ? 000000000 ? 000000000 ? 000000041 ? opiodr()+1184 CALL opiosq() 000000003 ? 00000000F ? 7FFFA35C5600 ? 000000000 ? 000000000 ? 000000041 ? ssd_unwind_bp: unhandled instruction at 0x24168a7 instr=f rpidrus()+196 CALL opiodr() 00000004A ? 00000000F ? 7FFFA35C5600 ? 000000005 ? 005BEBA90 ? 000000041 ? skgmstack()+158 CALL rpidrus() 7FFFA35C4E58 ? 00000000F ? 7FFFA35C5600 ? 000000005 ? 005BEBA90 ? 000000041 ? rpidru()+116 CALL skgmstack() 7FFFA35C4E30 ? 006AE9620 ? 00000F618 ? 002419628 ? 7FFFA35C4E58 ? 000000041 ? rpiswu2()+409 CALL rpidru() 7FFFA35C54F8 ? 006AE9620 ? 00000F618 ? 002419628 ? 7FFFA35C4E58 ? 000000041 ? rpidrv()+1516 CALL rpiswu2() 07F1AEA58 ? 000000000 ? 7FFFA35C54D8 ? 000000002 ? 7FFFA35C5540 ? 000000000 ? rpispl()+406 CALL rpidrv() 000000005 ? 00000004A ? 7FFFA35C5600 ? 000000008 ? 7FFFA35C5540 ? 000000000 ? tbsdrac()+4341 CALL rpispl() 000000005 ? 000000000 ? 0087315C0 ? 000000041 ? 000000000 ? 000000000 ? dtsdrv()+2882 CALL tbsdrac() 000000009 ? 000000000 ? 000000000 ? 000000001 ? 000000000 ? 000000000 ? opiexe()+14347 CALL dtsdrv() 000000009 ? 000000000 ? 000000000 ? 000000001 ? 000000000 ? 000000000 ? opiosq0()+3398 CALL opiexe() 000000004 ? 000000000 ? 7FFFA35C7B3C ? 000000002 ? 000000000 ? 000000000 ? kpooprx()+318 CALL opiosq0() 000000003 ? 00000000E ? 7FFFA35C7E68 ? 0000000A4 ? 000000000 ? 600000039 ? kpoal8()+783 CALL kpooprx() 7FFFA35CB04C ? 7FFFA35C9078 ? 000000039 ? 000000001 ? 000000000 ? 600000039 ? opiodr()+1184 CALL kpoal8() 00000005E ? 000000017 ? 7FFFA35CB048 ? 000000001 ? 000000001 ? 600000039 ? ttcpip()+1226 CALL opiodr() 00000005E ? 000000017 ? 7FFFA35CB048 ? 000000000 ? 005BEBDB0 ? 600000039 ? opitsk()+1310 CALL ttcpip() 006AF1FD0 ? 0054A67A0 ? 7FFFA35CB048 ? 000000000 ? 7FFFA35CAB48 ? 7FFFA35CB1B0 ? opiino()+1024 CALL opitsk() 000000003 ? 000000000 ? 7FFFA35CB048 ? 000000001 ? 000000000 ? 6AF000900000001 ? opiodr()+1184 CALL opiino() 00000003C ? 000000004 ? 7FFFA35CC248 ? 000000001 ? 000000000 ? 6AF000900000001 ? opidrv()+548 CALL opiodr() 00000003C ? 000000004 ? 7FFFA35CC248 ? 000000000 ? 005BEB860 ? 6AF000900000001 ? sou2o()+114 CALL opidrv() 00000003C ? 000000004 ? 7FFFA35CC248 ? 000000000 ? 005BEB860 ? 6AF000900000001 ? opimai_real()+163 CALL sou2o() 7FFFA35CC220 ? 00000003C ? 000000004 ? 7FFFA35CC248 ? 005BEB860 ? 6AF000900000001 ? main()+116 CALL opimai_real() 000000002 ? 7FFFA35CC2B0 ? 000000004 ? 7FFFA35CC248 ? 005BEB860 ? 6AF000900000001 ? __libc_start_main() CALL main() 000000002 ? 7FFFA35CC2B0 ? +253 000000004 ? 7FFFA35CC248 ? 005BEB860 ? 6AF000900000001 ? _start()+41 CALL __libc_start_main() 00072D134 ? 000000002 ? 7FFFA35CC408 ? 000000000 ? 005BEB860 ? 6AF000900000001 ? --------------------- Binary Stack Dump --------------------- |
可以看到，报错发生在删除一个表上。而这个表在数据库中应该是不存在的，或者说即使这个表存在，也不可能建立在这个表空间。那么导致问题的原因应该只有一个，数据字典出现了不一致的状态。
SQL> SELECT ts#, COUNT(*) FROM seg$ GROUP BY ts#; TS# COUNT(*) ---------- ---------- 1 11 6 20 2 1126 4 57 5 5 0 1429 6 ROWS selected. SQL> SELECT ts#, COUNT(*) FROM tab$ GROUP BY ts#; TS# COUNT(*) ---------- ---------- 6 429 11 97 2 247 5 5 4 48 8 30 7 2 0 736 9 10 12 2 10 ROWS selected. |
可以看到，在SEG$中不存在表空间编号大于6的对象，但是在TAB$中确存在很多。
SQL> SELECT ts#, name FROM ts$ WHERE name = 'ALA_TEST'; TS# NAME ---------- ------------------------------ 9 ALA_TEST SQL> SELECT ts#, name FROM ts$; TS# NAME ---------- ------------------------------ 0 SYSTEM 1 UNDOTBS 2 SYSAUX 3 TEMP 4 TBS_1 5 GGX 6 USERS 7 PERFSTAT 8 STATSPACK 9 ALA_TEST 10 ROWS selected. |
可以看到，表空间目前编号只到9，而TAB$中记录的表空间的编号以及到了12。显然这就是导致ORA-600错误的原因。Oracle在删除表空间的时候发现有表存在，但是根据段信息又找不到对应的记录，导致删除无法完成，从而引发了这个错误。
至于数据字典不一致的产生应该是测试环境通过imp导入tab$引起的，对于测试环境而言，这个错误很容易清除掉，直接删除不一致的记录即可，但是对于产品环境而言，不推荐这种方式：
SQL> SELECT ts#, COUNT(*) FROM ind$ GROUP BY ts#; TS# COUNT(*) ---------- ---------- 6 239 11 61 13 103 2 332 4 9 8 31 0 858 7 ROWS selected. SQL> DELETE tab$ WHERE ts# = 9; 10 ROWS deleted. SQL> commit; Commit complete. SQL> DROP tablespace ala_test including contents AND datafiles; Tablespace dropped. |