[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

--//虽然操作可以通过，但是因为是空表不会出现冲突情况。