ORA-600(17113)错误

又是一个PL/DEVELOPER的bug。
客户数据库是9.2.0.8，告警日志中出现大量下面的错误信息：

Thu DEC 1 08:37:52 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_1995124.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Thu DEC 1 08:37:52 2011
Trace dumping IS performing id=[cdmp_20111201083752]
Thu DEC 1 08:38:21 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_1995124.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Thu DEC 1 08:39:47 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_2982400.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Thu DEC 1 08:41:32 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_1995124.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Thu DEC 1 08:42:13 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_1995124.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Thu DEC 1 08:43:37 2011
Errors IN file /oracle9/app/admin/db/udump/db1_ora_1995124.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []

在详细TRACE文件/oracle9/app/admin/db/udump/db1_ora_1995124.trc中，可以看到这是一个DEVELOPER工具发起的会话：

   O/S info: USER: Administrator, term: PC2011100510, ospid: 2676:2564, machine: WORKGROUP\PC2011100510
              program: plsqldev.exe
    application name: PL/SQL Developer, hash VALUE=1190136663
    action name: Main SESSION, hash VALUE=1773317990

而导致错误的SQL语句为：

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;

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;

这显然是DEVELOPER在获取数据字典源数据时执行的SQL，关于这个错误的描述可以参考文档：ORA-00600: internal error code, arguments: [17113] using pl/sql developer [ID 396326.1]。
Oracle针对这个问题的解决方案只有一句，把pl/sql developer升级到最新版本。

ORA-600(17113)错误

Leave a Reply Cancel reply

Recent Posts

Recent Comments

Archives

Categories

Meta