分类目录归档：数据库

在巡检某运营商的计费库时，发现alert日志中发现如下错误

Thu Feb  2 13:54:52 2012
Errors in file /oracle9/app/admin/bill/udump/bill1_ora_35651918.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []

查看trace文件

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_bill_1
Release:        1
Version:        6
Machine:        00F64FF34C00
Instance name: bill1
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 35651918, image: oracle@zwq_bill_1 (TNS V1-V3)

*** 2012-02-02 13:54:52.169
*** SESSION ID:(210.1380) 2012-02-02 13:54:52.150
********** Internal heap ERROR 17113 addr=0 *********
******************************************************
HEAP DUMP heap name=""  desc=0
 extent sz=0x0 alt=0 het=0 rec=0 flg=0 opc=0
 parent=0 owner=0 nex=0 xsz=0x0
 Hla: -1
*** 2012-02-02 13:54:52.169
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Current SQL statement for this session:
declare
  t_owner varchar2(30);
  t_name  varchar2(30);
  procedure check_mview is
    dummy integer;
  begin
    if ：object_type = 'TABLE' then
      select 1 into dummy
      from sys.all_objects
      where owner = ：object_owner
      and object_name = ：object_name
      and object_type = 'MATERIALIZED VIEW'
      and rownum = 1;
      ：object_type := 'MATERIALIZED VIEW';
    end if;
  exception
    when others then null;
  end;
begin
  :sub_object := null;
  if :deep != 0 then
    begin
      if :part2 is null then
        select constraint_type, owner, constraint_name
          into ：object_type, ：object_owner, ：object_name
          from sys.all_constraints c
         where c.constraint_name = :part1 and c.owner = user
           and rownum = 1;
      else
        select constraint_type, owner, constraint_name, :part3
          into ：object_type, ：object_owner, ：object_name, :sub_object
          from sys.all_constraints c
         where c.constraint_name = :part2 and c.owner = :part1
           and rownum = 1;
      end if;
      if ：object_type = 'P' then ：object_type := 'PRIMARY KEY'; end if;
      if ：object_type = 'U' then ：object_type := 'UNIQUE KEY'; end if;
      if ：object_type = 'R' then ：object_type := 'FOREIGN KEY'; end if;
      if ：object_type = 'C' then ：object_type := 'CHECK CONSTRAINT'; end if;        
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  :sub_object := :part2;
  if (:part2 is null) or (:part1 != user) then
    begin
      select object_type, user, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects
      where owner = user
      and object_name = :part1
      and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
      and rownum = 1;
      if ：object_type = 'SYNONYM' then
        select s.table_owner, s.table_name
          into t_owner, t_name
          from sys.all_synonyms s
         where s.synonym_name = :part1
           and s.owner = user
           and rownum = 1;
        select o.object_type, o.owner, o.object_name
          into ：object_type, ：object_owner, ：object_name
          from sys.all_objects o 
         where o.owner = t_owner
           and o.object_name = t_name
           and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
           and rownum = 1;
      end if;
      :sub_object := :part2;
      if :part3 is not null then
        :sub_object := :sub_object || '.' || :part3;
      end if;
      check_mview;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  begin
    select s.table_owner, s.table_name
      into t_owner, t_name
      from sys.all_synonyms s
     where s.synonym_name = :part1
       and s.owner = 'PUBLIC'
       and rownum = 1;
    select o.object_type, o.owner, o.object_name
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects o 
     where o.owner = t_owner
       and o.object_name = t_name
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  :sub_object := :part3;
  begin
    select o.object_type, o.owner, o.object_name
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects o
     where o.owner = :part1
       and o.object_name = :part2
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null
    then
      select 'USER', null, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.all_users u
      where u.username = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null and :deep != 0
    then
      select 'ROLE', null, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.session_roles r
      where r.role = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  ：object_owner := null;
  ：object_type := null;
  ：object_name := null;
  :sub_object := null;
end;

通过观察trace的这部分可以知道，是执行这条sql语句导致了ora-600[17113]错误的产生，而这条语句是查询系统对象，初步怀疑是oracle客户端上产生，继续阅读trace文件发现

    SO: 7000000b926f188, type: 4, owner: 7000000bd298b00, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000000bd298b00, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-00C8-0009F6EF, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 47, prv: 0, sql: 7000000d481ee78, psql: 7000000e086d8d8, user: 567/IBILLAPP
    O/S info: user: Administrator, term: WWW-39A255460E8, ospid: 784:2080, machine: WORKGROUP\WWW-39A255460E8
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: Main session, hash value=1773317990
    last wait for 'SQL*Net more data from client' blocking sess=0x0 seq=78 wait_time=4
                driver id=54435000, #bytes=34, =0
    temporary object counter: 0

从这里进一步确认是有人使用PL/SQL Developer从WORKGROUP\WWW-39A255460E8的机器上操作该数据库导致，查询mos，果然发现该问题记录[ID 396326.1]。
解决办法：使用新版本的plsql dev工具即可解决问题。