[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