bug – 第 2 页 – OracleBlog

遭遇ora-600[20022]

2008-09-23 没有评论

在近期进行十一数据库巡检的时候，某省的同事发现在数据库的alertlog中有关于ora-600的报错：

Sun Sep 21 19:20:52 2008
Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13668.trc:
ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []
Sun Sep 21 19:21:15 2008
Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13072.trc:
ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []
Sun Sep 21 19:38:09 2008

Sun Sep 21 19:20:52 2008

Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13668.trc:

ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []

Sun Sep 21 19:21:15 2008

Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13072.trc:

ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []

Sun Sep 21 19:38:09 2008

进一步查看了trace文件，发现是一个sql有关：

*** SESSION ID:(172.1955) 2008-09-21 19:20:52.752
*** 2008-09-21 19:20:52.752
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []
Current SQL statement for this session:
select count(*)  into :b0  from (select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(enddate,'YYYYMMDDHH24MISS') enddatetime ,tab_a.status status1 ,b.subsid subsid2 ,decode(nvl(d.status,'A'),'A',0,2) status2 ,tab_a.visitcount visitcount  from tab_a ,tab_b a ,icp ,service ,tab_b b ,tab_c d where (((((((nvl(tab_a.chargemid,tab_a.mid)=a.mid and tab_a.icpid=icp.icpid) and tab_a.servid=service.servid) and tab_a.status<>'C') and (tab_a.mid=:b1 or tab_a.chargemid=:b1)) and tab_a.mid=b.mid) and tab_a.mid=d.mid(+)) and tab_a.servid=d.servid(+)) union select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(startdate,'YYYYMMDDHH24MISS') enddatetime ,'A' status1 ,a.subsid subsid2 ,1 status2 ,tab_a_freeuse.visitcount visitcount  from tab_a_freeuse ,tab_b a ,icp ,service where (((tab_a_freeuse.icpid=icp.icpid and tab_a_freeuse.servid=service.servid) and tab_a_freeuse.mid=:b1) and tab_a_freeuse.mid=a.mid)) 
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp()+184         ?        ksedst()             40000000017F5B37 ?
                                                   000000032 ?
                                                   800003FAC0006570 ?
                                                   000000000 ?
……

*** SESSION ID:(172.1955) 2008-09-21 19:20:52.752

*** 2008-09-21 19:20:52.752

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], []

Current SQL statement for this session:

select count(*) into :b0 from (select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(enddate,'YYYYMMDDHH24MISS') enddatetime ,tab_a.status status1 ,b.subsid subsid2 ,decode(nvl(d.status,'A'),'A',0,2) status2 ,tab_a.visitcount visitcount from tab_a ,tab_b a ,icp ,service ,tab_b b ,tab_c d where (((((((nvl(tab_a.chargemid,tab_a.mid)=a.mid and tab_a.icpid=icp.icpid) and tab_a.servid=service.servid) and tab_a.status<>'C') and (tab_a.mid=:b1 or tab_a.chargemid=:b1)) and tab_a.mid=b.mid) and tab_a.mid=d.mid(+)) and tab_a.servid=d.servid(+)) union select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(startdate,'YYYYMMDDHH24MISS') enddatetime ,'A' status1 ,a.subsid subsid2 ,1 status2 ,tab_a_freeuse.visitcount visitcount from tab_a_freeuse ,tab_b a ,icp ,service where (((tab_a_freeuse.icpid=icp.icpid and tab_a_freeuse.servid=service.servid) and tab_a_freeuse.mid=:b1) and tab_a_freeuse.mid=a.mid))

----- Call Stack Trace -----

calling call entry argument values in hex

location type point (? means dubious value)

-------------------- -------- -------------------- ----------------------------

ksedmp()+184 ? ksedst() 40000000017F5B37 ?

000000032 ?

800003FAC0006570 ?

000000000 ?

……

为什么一个s […]

..troubleshooting, Working case

难道是rebuild index的一个bug？

2008-03-11 4条评论

今天在分析一条语句的时候，一个表也就10来行记录，segment的大小也就5M，通过查看其执行计划，发现是走索引的，但是却有5000多的cr。奇怪，之前表刚刚做过move tablespace and rebuild index，按理说rebuild index之后，10来行记录的cr不会有5000 […]

..troubleshooting, Working case

deadlock引起数据库挂死

2008-02-07 没有评论

某天，应用程序突然挂了，程序中报错连不上数据库。登录数据库主机后，发现sqlplus也登录不了，登录时，sqlplus长时间没有响应，检查alertlog只是发现半小时前检测到一个deadlock，没有其他的报错信息。心想oracle检测到deadlock能自动解锁，因此一开始没怀疑到是因为dead […]

Study note

BUG：9204中cast不能返回正确的值

2008-01-20 没有评论

问题是这样引起的：测试建一个single table hash cluster，建了好几次，也查了不少资料，一般报错 ORA-01753 是因为设置的字段类型不一致，但是经检查字段类型确实是一致了，且number也被cast定成了整形，应该没问题呀，但是总是报错： [crayon-673ef2946 […]

..troubleshooting, Working case

BUG:truncate分区后，segment大小没降低

2007-09-26 没有评论

今天遇到了一个的问题，一个分区表，当我truncate完一个分区，通过查看dba_segments.bytes竟然没有空间被释放：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
 
SQL> CREATE TABLE xxx
  2  (aa  varchar2(20),
  3  bb number ) tablespace tt
  4  PARTITION BY RANGE (bb)
  5  (PARTITION xxx_p1
  6  VALUES LESS THAN (10000),
  7  PARTITION xxx_p2
  8  VALUES LESS THAN (40000),
  9  PARTITION xxx_p3
 10  VALUES LESS THAN (80000),
 11  PARTITION xxx_p4
 12  VALUES LESS THAN (100000),
 13  PARTITION xxx_max
 14  values less than (maxvalue)
 15  );

Table created.


SQL> select TABLESPACE_NAME from user_tab_partitions where table_name='XXX';

TABLESPACE_NAME
------------------------------
TT
TT
TT
TT
TT

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          1      65536
         65536          1      65536
         65536          1      65536
         65536          1      65536
         65536          1      65536

SQL> insert into xxx select 'aa',rownum from dba_objects;

97390 rows created.

SQL> insert into xxx select * from xxx;

97390 rows created.

SQL> insert into xxx select aa,rownum  from xxx;

194780 rows created.

SQL> commit;

Commit complete.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752
         65536         17    2097152
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

SQL> alter table xxx truncate partition xxx_p1;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752   ######truncate该分区，空间没释放
         65536         17    2097152
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

SQL> alter table xxx truncate partition xxx_p2;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT    EXTENTS      BYTES
-------------- ---------- ----------
         65536          7     458752
         65536         17    2097152    ######truncate该分区，空间没释放
         65536         17    2097152
         65536         13     851968
         65536         17    2097152

100

101

102

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for HPUX: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 - Production

SQL> CREATE TABLE xxx

2 (aa varchar2(20),

3 bb number ) tablespace tt

4 PARTITION BY RANGE (bb)

5 (PARTITION xxx_p1

6 VALUES LESS THAN (10000),

7 PARTITION xxx_p2

8 VALUES LESS THAN (40000),

9 PARTITION xxx_p3

10 VALUES LESS THAN (80000),

11 PARTITION xxx_p4

12 VALUES LESS THAN (100000),

13 PARTITION xxx_max

14 values less than (maxvalue)

15 );

Table created.

SQL> select TABLESPACE_NAME from user_tab_partitions where table_name='XXX';

TABLESPACE_NAME

------------------------------

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

-------------- ---------- ----------

65536 1 65536

SQL> insert into xxx select 'aa',rownum from dba_objects;

97390 rows created.

SQL> insert into xxx select * from xxx;

97390 rows created.

SQL> insert into xxx select aa,rownum from xxx;

194780 rows created.

SQL> commit;

Commit complete.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

-------------- ---------- ----------

65536 7 458752

65536 17 2097152

65536 13 851968

65536 17 2097152

SQL> alter table xxx truncate partition xxx_p1;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

-------------- ---------- ----------

65536 7 458752 ######truncate该分区，空间没释放

65536 17 2097152

65536 13 851968

65536 17 2097152

SQL> alter table xxx truncate partition xxx_p2;

Table truncated.

SQL> select INITIAL_EXTENT,EXTENTS,BYTES from user_segments where segment_name='XXX';

INITIAL_EXTENT EXTENTS BYTES

-------------- ---------- ----------

65536 7 458752

65536 17 2097152 ######truncate该分区，空间没释放

65536 17 2097152

65536 13 851968

65536 17 2097152

但是在我机器上的数据库中进行测试，却是有正常的结果： [crayon-673ef2946a0d3236 […]

« 上一页 1 2