[20201109]11.2.0.4增加欄位與預設值問題.txt

[20201109]11.2.0.4增加欄位與預設值問題.txt

--//連結：http://www.itpub.net/thread-2138876-1-1.html。

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試例子：

create table t1 as select * from dept;
create table t2 as select * from dept;

SCOTT@book> alter table t1 add col1 varchar2(2000) not null default ' ';
alter table t1 add col1 varchar2(2000) not null default ' '
                                                        *
ERROR at line 1:
ORA-30649: missing DIRECTORY keyword


SCOTT@book> alter table t2 add col1 varchar2(2000) not null default '';
alter table t2 add col1 varchar2(2000) not null default ''
                                                        *
ERROR at line 1:
ORA-30649: missing DIRECTORY keyword

$ oerr ora 30649
30649,0000, "missing DIRECTORY keyword"
// *Cause:  DEFAULT DIRECTORY clause missing or incorrect.
// *Action: Provide the DEFAULT DIRECTORY.

--//修改如下：
SCOTT@book> alter table t1 add col1 varchar2(2000)  default ' ' not null;
Table altered.

SCOTT@book> alter table t2 add col1 varchar2(2000)  default '' not null;
Table altered.

3.測試：
SCOTT@book> select col1 c10, dump(col1) c20, length(col1) n10 from t1 where rownum=1;
C10        C20                                    N10
---------- -------------------- ---------------------
           Typ=1 Len=1: 32                          1

SCOTT@book> select col1 c10, dump(col1) c20, length(col1) n10 from t2 where rownum=1;
C10        C20                                    N10
---------- -------------------- ---------------------
           NULL

SCOTT@book> @desc t2
Name                            Null?    Type
------------------------------- -------- ----------------------------
DEPTNO                                   NUMBER(2)
DNAME                                    VARCHAR2(14)
LOC                                      VARCHAR2(13)
COL1                            NOT NULL VARCHAR2(2000)

--//T2的欄位col1記錄的NULL，明顯與表定義存在衝突。我記憶裡yangtingkun好像也寫過類似的文章。
--//連結：yangtingkun.net/?p=1483
--//我當時的測試：http://blog.itpub.net/267265/viewspace-2121942/=>[20160713]修改表結構增加1列與預設值.txt

SCOTT@book> select * from t2 where col1 is null;
no rows selected
--//沒有輸出。過濾條件是   1 - filter(NULL IS NOT NULL)。而這樣查詢就出現：

SCOTT@book> set NULL null
SCOTT@book> select deptno,col1 c10 ,dump(col1,16) c20 from t2 where col1 is not null;
    DEPTNO C10        C20
---------- ---------- --------------------
        10 null       NULL
        20 null       NULL
        30 null       NULL
        40 null       NULL
--//查詢條件與儲存值存在衝突，實際上這樣修改表結構很快，而實際的值記錄在sys.ecol$資料欄位中。

SCOTT@book> insert into t1 values (11,'a','b',null);
insert into t1 values (11,'a','b',null)
                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T1"."COL1")


SCOTT@book> insert into t2 values (11,'a','b',null);
insert into t2 values (11,'a','b',null)
                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T2"."COL1")
--//不能插入NULL。

SCOTT@book>  column BINARYDEFVAL format a30
SCOTT@book> select * from sys.ecol$;
   TABOBJ#     COLNUM BINARYDEFVAL
---------- ---------- ------------------------------
     94552          4 20

--//BINARYDEFVAL=20,ascii碼十進位制就是32，也就是對應空格。可以發現僅僅一個物件。

SCOTT@book> select * from dba_objects where object_id='94552'
  2  @ prxx
==============================
OWNER                         : SCOTT
OBJECT_NAME                   : T1
SUBOBJECT_NAME                :
OBJECT_ID                     : 94552
DATA_OBJECT_ID                : 94552
OBJECT_TYPE                   : TABLE
CREATED                       : 2020-11-09 10:06:29
LAST_DDL_TIME                 : 2020-11-09 10:12:04
TIMESTAMP                     : 2020-11-09:10:12:04
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 1
EDITION_NAME                  :
PL/SQL procedure successfully completed.

--//在sys.ecol$中並沒有T2表的定義，這樣就出現歧義，不知道其它版本是否有這樣的情況。

4.在高版本的測試：
SYS@orcl> select banner from v$version where rownum=1;
BANNER
----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SYS@orcl> create table t1 (a number);
Table created.

SYS@orcl> insert into t1 values (1);
1 row created.

SYS@orcl> commit ;
Commit complete.

SYS@orcl> alter table t1 add col1 varchar2(2000)  default '' not null;
alter table t1 add col1 varchar2(2000)  default '' not null
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

--//oracle高版本已經修復該錯誤。

SYS@orcl> drop table t1 purge ;
Table dropped.

SYS@orcl> create table t1 (a number);
Table created.

SYS@orcl> alter table t1 add col1 varchar2(2000)  default '' not null;
Table altered.
--//在空表的情況下可以透過。

SYS@orcl> desc t1
Name   Null?    Type
------ -------- --------------
A               NUMBER
COL1   NOT NULL VARCHAR2(2000)

SYS@orcl> select * from sys.ecol$;
no rows selected

--//雖然操作可以透過，但是因為是空表不會出現衝突情況。