[20201111]11.2.0.4版本仍然存在的一个未修复Bug ora-00600 [13013].txt

[20201111]11.2.0.4版本仍然存在的一个未修复Bug ora-00600 [13013].txt http://www.killdb.com/2020/05/18/11-2-0-4%e7%89%88%e6%9c%ac%e4%bb%8d%e7%84%b6%e5%ad%98%e5%9c%a8%e7%9a%84%e4%b8%80%e4%b8%aa%e6%9c%aa%e4%bf%ae%e5%a4%8dbug-ora-00600-13013/ ORA-00600: internal error code, arguments: [13013], [5001], [3636415], [98890439], [54], [98890439], [17], [], [], [], [], [] 针对该错误，Oracle mos有文档进行了解释，这里不多说： ORA-600 [13013] [a] [b] [c] [d] [e] [f] Arg [a] Passcount Arg [b] Data Object number Arg [c] Tablespace Relative DBA of block containing the row to be updated Arg [d] Row Slot number Arg [e] Relative DBA of block being updated (should be same as [c]) Arg [f] Code --//98890439 = set dba 23,2421447 = alter system dump datefile 23 block 2421447 = 0x5e4f2c7 --//链接提供例子，可以重复演示这个bug： CREATE TABLE TAB1 ( ID_NACE NUMBER(5) ); insert into TAB1 values(0); ALTER TABLE TAB1 ADD (ID_INDUSTRY  NUMBER(5) DEFAULT -1 NOT NULL); ALTER TABLE TAB1 ADD (ID_ind2  varchar2(1) DEFAULT 'U' NOT NULL ); ALTER TABLE TAB1 ADD ( err  varchar2(30)); insert into TAB1 values(1, 1, 'C',null); insert into TAB1 values(2, 2, 'C',null); CREATE TABLE TAB2 (ID_NACE NUMBER(5),ID_INDUSTRY  NUMBER(5)); insert into TAB2 values(1, 3); commit; update tab1 set err = 'xxxxx' where id_ind2 = 'C' and not exists (select 1 from tab2 where tab2.id_nace = tab1.id_nace and tab2.ID_INDUSTRY = tab1.ID_INDUSTRY); update tab1        * ERROR at line 1: ORA-00600: internal error code, arguments: [13013], [5001], [94594], [16779067], [1], [16779067], [17], [], [], [], [], [] --//16779067 = set dba 4,1851 = alter system dump datefile 4 block 1851 = 0x100073b SCOTT@book> select rowid,tab1.* from tab1; ROWID                 ID_NACE ID_INDUSTRY I ERR ------------------ ---------- ----------- - ------------------------------ AAAXGCAAEAAAAc7AAA          0          -1 U AAAXGCAAEAAAAc7AAB          1           1 C AAAXGCAAEAAAAc7AAC          2           2 C SCOTT@book> @ rowid AAAXGCAAEAAAAc7AAA     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      94594          4       1851          0  0x100073B           4,1851               alter system dump datafile 4 block 1851 SCOTT@book> select * from dba_objects where object_name like 'TAB%' and owner=user   2  @ prxx ============================== OWNER                         : SCOTT OBJECT_NAME                   : TAB1 SUBOBJECT_NAME                : OBJECT_ID                     : 94594 DATA_OBJECT_ID                : 94594 OBJECT_TYPE                   : TABLE CREATED                       : 2020-11-11 10:18:09 LAST_DDL_TIME                 : 2020-11-11 10:18:19 TIMESTAMP                     : 2020-11-11:10:18:19 STATUS                        : VALID TEMPORARY                     : N GENERATED                     : N SECONDARY                     : N NAMESPACE                     : 1 EDITION_NAME                  : ============================== OWNER                         : SCOTT OBJECT_NAME                   : TAB2 SUBOBJECT_NAME                : OBJECT_ID                     : 94595 DATA_OBJECT_ID                : 94595 OBJECT_TYPE                   : TABLE CREATED                       : 2020-11-11 10:18:28 LAST_DDL_TIME                 : 2020-11-11 10:18:28 TIMESTAMP                     : 2020-11-11:10:18:28 STATUS                        : VALID TEMPORARY                     : N GENERATED                     : N SECONDARY                     : N NAMESPACE                     : 1 EDITION_NAME                  : PL/SQL procedure successfully completed. --//这个11g新特性增加字段缺省值导致的问题。 SYS@book> @ hide _add_col_optim_enabled NAME                   DESCRIPTION                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---------------------- ---------------------------------- ------------- ------------- ------------ ----- --------- _add_col_optim_enabled Allows new add column optimization TRUE          TRUE          TRUE         TRUE  IMMEDIATE SYS@book> @ prxx ============================== NAME                          : _add_col_optim_enabled DESCRIPTION                   : Allows new add column optimization DEFAULT_VALUE                 : TRUE SESSION_VALUE                 : TRUE SYSTEM_VALUE                  : TRUE ISSES_MODIFIABLE              : TRUE ISSYS_MODIFIABLE              : IMMEDIATE PL/SQL procedure successfully completed. SCOTT@book> alter session set "_add_col_optim_enabled"=false; Session altered. /* Formatted on 2020/11/11 10:30:33 (QP5 v5.269.14213.34769) */ UPDATE Tab1    SET err = 'xxxxx'  WHERE     id_ind2 = 'C'        AND NOT EXISTS               (SELECT 1                  FROM tab2                 WHERE     tab2.id_nace = tab1.id_nace                       AND tab2.ID_INDUSTRY = tab1.ID_INDUSTRY); --//我的测试修改参数也无效。噢不对，这个参数在alter table时才有效。 --//比较笨的方法就是重新建立表才执行就可以通过。 SCOTT@book> create table tab1x as select * from tab1; Table created. SCOTT@book> rename tab1 to tab1y; Table renamed. SCOTT@book> rename tab1x to tab1; Table renamed. UPDATE Tab1    SET err = 'xxxxx'  WHERE     id_ind2 = 'C'        AND NOT EXISTS               (SELECT 1                  FROM tab2                 WHERE     tab2.id_nace = tab1.id_nace   AND tab2.ID_INDUSTRY = tab1.ID_INDUSTRY); 2 rows updated. --//OK现在修改成功!! SCOTT@book> select * from tab1;    ID_NACE ID_INDUSTRY I ERR ---------- ----------- - ------------------------------          0          -1 U          1           1 C xxxxx          2           2 C xxxxx --//查询那些对象有这样情况，查询字典sys.ecol$。 SCOTT@book> column BINARYDEFVAL format a40 SCOTT@book> select * from sys.ecol$;    TABOBJ#     COLNUM BINARYDEFVAL ---------- ---------- ----------------------------------------      94594          2 3E6466      94594          3 55