Oracle大表添加字段

Oracle大表添加字段

在添加字段时，如果表数据量特别大。给添加的字段设置默认值，会消耗特别长的时间，而且所有行都要被锁住。

在生产系统上，如果没有安排足够长的变更时间，可能会对业务带来严重的影响。

对于这种情况，可以使用下面的语句:

alter table table_name add new_column data_type(precision) default 'value_string' not null;

也就是default + not null 这两个关键词组合的方式。

使用这种方法添加的字段，并不会真的修改每行的值，而是在sys.ecol$ 里添加一行数据，记录下该字段的默认值。

虽然没有进行物理存储，但是对查询、数据泵等操作都没有影响 。

可以看下面示例:

SQL> col tabobj# for 999999999
SQL> col column for a20
SQL> col binarydefval for 99999999999999999999
SQL> select * from sys.ecol$;

未选定行

SQL> alter table test_object add create_user varchar2(30) default 'halberd' not null;

表已更改。

SQL> col tabobj# for 999999999
SQL> col column for a20
SQL> col defval for a15
SQL> select tabobj#,COLNUM, utl_raw.cast_to_varchar2(binarydefval) as defval from sys.ecol$;

   TABOBJ#     COLNUM DEFVAL
---------- ---------- ---------------
    247703         27 halberd

SQL> select object_id,object_name from dba_objects where object_name='TEST_OBJECT';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
    247703 TEST_OBJECT

SQL> select column_id from dba_tab_cols where owner='SYSTEM' and table_name='TEST_OBJECT' AND COLUMN_NAME='CREATE_USER';

 COLUMN_ID
----------
        27

这里我们验证了，该方式添加字段后，在sys.ecol$ 里插入了一行数据 。

下面验证，数据并没有存储到物理行中。

SQL> select max(column_id) from dba_tab_cols where table_name='TEST_OBJECT' AND OWNER='SYSTEM';

MAX(COLUMN_ID)
--------------
            27

SQL> update test_object set create_user='halberd' where rownum=1;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select rowid,dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) rfno,
  2    3  dbms_rowid.rowid_block_number(rowid) rdbno,
  4  dbms_rowid.rowid_row_number(rowid) rno from test_object WHERE ROWNUM<=2;


ROWID               OBJECT_ID       RFNO      RDBNO        RNO
------------------ ---------- ---------- ---------- ----------
AAA8eXAABAADz9BAAA     247703          1     999233          0
AAA8eXAABAADz9BAAB     247703          1     999233          1


SQL> select file_id,relative_fno from cdb_data_files where relative_fno=1;

   FILE_ID RELATIVE_FNO
---------- ------------
       199            1

SQL> alter system dump datafile 199 block 999233;

system altered.

从上面的查询结果来看，表test_object 有27个字段。我添加的新字段，就是第27个字段。如果是dump数据块出来，显示的就是row 26, 因为dump 结果是从row 0 开始的。

下面是dump的数据块中前两行的的内容:

block_row_dump:
tab 0, row 0, @0x3f5
tl: 114 fb: --H-FL-- lb: 0x3  cc: 27
col  0: [ 3]  53 59 53
col  1: [14]  49 5f 46 49 4c 45 23 5f 42 4c 4f 43 4b 23
col  2: *NULL*
col  3: [ 2]  c1 0a
col  4: [ 2]  c1 0a
col  5: [ 5]  49 4e 44 45 58
col  6: [ 7]  78 77 04 11 01 39 0f
col  7: [ 7]  78 77 04 11 01 39 0f
col  8: [19]  32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 05
col 14: *NULL*
col 15: [ 4]  4e 4f 4e 45
col 16: *NULL*
col 17: [ 1]  59
col 18: [ 1]  4e
col 19: *NULL*
col 20: [ 1]  4e
col 21: [ 1]  4e
col 22: *NULL*
col 23: *NULL*
col 24: *NULL*
col 25: *NULL*
col 26: [ 7]  68 61 6c 62 65 72 64
tab 0, row 1, @0x1ec4
tl: 94 fb: --H-FL-- lb: 0x0  cc: 22
col  0: [ 3]  53 59 53
col  1: [ 6]  49 5f 4f 42 4a 33
col  2: *NULL*
col  3: [ 2]  c1 27
col  4: [ 2]  c1 27
col  5: [ 5]  49 4e 44 45 58
col  6: [ 7]  78 77 04 11 01 39 0f
col  7: [ 7]  78 77 04 11 01 39 0f
col  8: [19]  32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 05
col 14: *NULL*
col 15: [ 4]  4e 4f 4e 45
col 16: *NULL*
col 17: [ 1]  59
col 18: [ 1]  4e
col 19: *NULL*
col 20: [ 1]  4e
col 21: [ 1]  4e
tab 0, row 2, @0x1e67

第一行, tab 0, row 0, @0x3f5 , 最后一个字段是第27个字段(dump 出来的字段数行数据都是从0 开始的)。

SQL> select utl_raw.cast_to_varchar2(replace('68 61 6c 62 65 72 64',' ')) as val from dual;

VAL
--------------------------------------------------------------------------------
halberd

而第二行, tab 0, row 1, @0x1ec4 ,则仅仅dump 出22个字段。说明22个字段之后的内容根本没有存储在数据块中，所以dump的结果没有这些字段的内容。

Author: halberd.lee

Created: 2020-09-16 Wed 10:38

Validate