ORCLE 12C 增加列,无默认值

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORCLE 12C 增加列,无默认值

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对11gR2比较熟悉的朋友应该比较清楚,在该版本中引入了一个新的特性,能够快速的增加一个新列,具体见Oracle 11g增加列,并带默认值的新特性,但是这个功能在该版本中总有个不足,需要设置默认值,在有些情况下,有些列就是不需要默认值,在12C的版本中,解决了这个鸡肋,能够快速增加一个列而且可以是不指定默认值(默认值为NULL)
数据库12C版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(20));

Table created.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)

SQL> insert into t_xifenfei values (1,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (2,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values (3,'www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
         2 www.xifenfei.com
         3 www.xifenfei.com

第一次dump block

SQL> select   rowid, 
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xifenfei ;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAEy3AAEAAAAGGAAA          4        390          0
AAAEy3AAEAAAAGGAAB          4        390          1
AAAEy3AAEAAAAGGAAC          4        390          2

SQL> alter system dump datafile 4 block 390;

System altered.

--dump block 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
end_of_block_dump

增加不含默认值列

SQL> alter table t_xifenfei add c_xff varchar2(100);

Table altered.

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 C_XFF                                              VARCHAR2(100)
--证明增加列无默认值

SQL> insert into t_xifenfei values(4,'www.xifenfei.com','www.orasos.com');

1 row created.

SQL> commit;

Commit complete.

第二次dump block

SQL> alter system checkpoint;

System altered.

SQL>  alter system dump datafile 4 block 390;

System altered.

--block dump 
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f2d
tl: 38 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 05
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
col  2: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
end_of_block_dump

从这里可以明显的看出来,前面的三条记录只有2列,但是四条记录有3列,证明使用了11gR2的新特性,这里可以使用null的默认值,证明比以往版本新特性增强.

此条目发表在 ORACLE 12C 分类目录,贴了 标签。将固定链接加入收藏夹。

ORCLE 12C 增加列,无默认值》有 1 条评论

  1. 惜分飞 说:

    试验测试证明在11.2.0.3的版本中就已经具有了Metadata-Only DEFAULT Column Values for NULL Columns,而不是从12C开始的新特性

    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Solaris: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> drop table t_xifenfei;
    
    Table dropped.
    
    SQL> create table t_xifenfei(id number,name varchar2(20));
    
    Table created.
    
    SQL> insert into t_xifenfei values (1,'www.xifenfei.com');
    
    1 row created.
    
    SQL> insert into t_xifenfei values (2,'www.xifenfei.com');
    
    1 row created.
    
    SQL> insert into t_xifenfei values (3,'www.xifenfei.com');
    
    1 row created.
    
    SQL> alter table t_xifenfei add c_xff1 varchar2(100);
    
    Table altered.
    
    SQL> alter table t_xifenfei add c_xff2 varchar2(100) default 'www.xifenfei.com' not null;
    
    Table altered.
    
    SQL> desc t_xifenfei;
     Name                                                              Null?    Type
     ----------------------------------------------------------------- -------- ----------------------
     ID                                                                         NUMBER
     NAME                                                                       VARCHAR2(20)
     C_XFF1                                                                     VARCHAR2(100)
     C_XFF2                                                            NOT NULL VARCHAR2(100)
    
    SQL> select   rowid,
      2    dbms_rowid.rowid_relative_fno(rowid)rel_fno,
      3   dbms_rowid.rowid_block_number(rowid)blockno,
      4   dbms_rowid.rowid_row_number(rowid) rowno
      5   from t_xifenfei ;
    
    ROWID                 REL_FNO    BLOCKNO      ROWNO
    ------------------ ---------- ---------- ----------
    AAANnzAAEAAHbekAAA          4    1947556          0
    AAANnzAAEAAHbekAAB          4    1947556          1
    AAANnzAAEAAHbekAAC          4    1947556          2
    
    SQL> alter system checkpoint;
    
    System altered.
    
    SQL> alter system dump datafile 4 block 1947556;
    
    System altered.
    
    --dump block
    block_row_dump:
    tab 0, row 0, @0x1f81
    tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
    tab 0, row 1, @0x1f6a
    tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 03
    col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
    tab 0, row 2, @0x1f53
    tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 04
    col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
    end_of_block_dump