[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 --//虽然操作可以通过，但是因为是空表不会出现冲突情况。