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的结果没有这些字段的内容。
Created: 2020-09-16 Wed 10:38