Oracle删除字段的方式和风险，你都了解么？

bisal的个人杂货铺 2020-05-25

1115

Oracle中和字段相关的知识还是很多的，不要小瞧了字段的增删改，一个小小的字段操作，一旦不清楚他的原理，随意在生产环境中执行，就可能产生让你印象深刻的影响。

一些和字段操作相关的历史文章，

《新增字段的一点一滴技巧》

《Oracle/EDB/达梦，对同字段多索引的支持》

《探寻大表删除字段慢的原因》

《大表删除字段为何慢？》

《alter table新增字段操作究竟有何影响？(下篇)》

《新增非空约束字段在不同版本中的演进》

《alter table新增字段操作究竟有何影响？(上篇)》

墨天轮这篇文章，《oracle中drop column的几种方式和风险》，讲了Oracle中大表删除字段的一些场景，从理论到实践，都很值得借鉴，

P.S. https://www.modb.pro/db/24497

生产环境的一张大表上执行了一个drop column，导致业务停止运行几个小时，这样的事情发生任何一位DBA身上，都会有一种深秋的悲凉。你是否足够了解在生产环境上执行drop column操作或者类似的DDL操作有多危险？本文通过一组模拟测试，观察几种不同的drop column方式对业务的影响。

测试脚本准备

create table t_test_col(ids number,dates date,vara varchar2(2000),varb varchar2(2000),varc varchar2(2000),vard varchar2(2000))PARTITION BY RANGE (dates) INTERVAL (numtodsinterval(1, 'day'))(partition part_t01 values less than(to_date('2000-11-01', 'yyyy-mm-dd')));;--插入200万测试数据begin  for i in 1..2000000 loop    insert into t_test_col values(i,sysdate-mod(i,100),'abc_aaaa','abc_bbbb','abc_cccc','abc_dddd');    if mod(i,1000)=0 then      commit;    end if;  end loop;  commit;end;--业务模拟程序1，每0.1秒执行一次插入，并记录日志表declare  l_cnt integer;  l_var varchar2(2000);begin  for i in 1..10000 loop    begin      insert into t_test_col(ids,dates,vara) values(i,sysdate,'test_aaaa');      rollback;      insert into t_log(dates,vars) values(systimestamp,'INSERT--ok');      commit;    exception      when others then        l_var:=substr(sqlerrm,1,1000);        insert into t_log(dates,vars) values(systimestamp,'INSERT--'||l_var);        commit;    dbms_lock.sleep(0.1);  end loop;end;--业务模拟程序2，每0.1秒执行一次查询，并记录日志表declare l_cnt integer;  l_var varchar2(2000);beginfor i in 1..10000 loop    begin      SELECT COUNT(1) INTO L_CNT from t_test_col where rownum=1;      insert into t_log(dates,vars) values(systimestamp,'SELECT--ok');      commit;    exception      when others then        l_var:=substr(sqlerrm,1,1000);        insert into t_log(dates,vars) values(systimestamp,'SELECT--'||l_var);        commit;    end;    dbms_lock.sleep(0.1);  end loop;end;

场景一：直接drop column

运行业务模拟程序，开始正常插入日志，然后删除大表的字段。

alter table t_test_col drop column vard;

影响范围：

1. drop column操作耗时30多秒。

2. insert 语句在drop column完成之前无法执行，等待事件为enq:TM-contention。

3. select不受影响。

场景二：先set unused然后再drop

alter table t_test_col set unused column vard;alter table t_test_col drop unused columns;

set unused仅更新表的数据字典，先将字段置为不可用状态，drop unused操作时才更新数据内容。

影响范围：

与场景一完全相同。

注意上述两种方式还会遇到一个非常麻烦的问题，在执行drop column的过程中，需要修改每一行数据，运行时间往往特别长，这会消耗大量的undo表空间，如果表特别大，操作时间足够长，undo表空间会全部耗尽。为了解决这个问题，有了第三种场景。

场景三：先set unused然后再drop column checkpoint

alter table t_test_col set unused column vard;alter table t_test_col drop unused columns checkpoint 1000;

drop unused columns checkpoint操作是每删除多少条记录，做一次提交，避免UNDO爆掉。这是一个好的解决思路，但是它带来的风险也是非常大的。这个操作在间隔分区上执行会命中BUG：20598042，ALTER TABLE DROP COLUMN … CHECKPOINT on an INTERVAL PARTITIONED table fails with ORA-600 [17016]。

执行结果是：

1. drop column checkpoint操作会报ORA-600[17016]错误。

2. 插入和查询操作，在drop过程以及drop报错之后，均抛出ORA-12986异常。

3. 在打补丁修复bug之前，这个表将无法正常使用。

换成普通分区表，先set unused然后再drop column checkpoint，

alter table t_test_col_2 set unused column vard;alter table t_test_col_2 drop unused columns checkpoint 1000;

影响范围：

1. insert 和select在drop column操作完成之前均无法执行。

2. 等待事件为library cache lock。

场景四: 使用DBMS_REDEFINITION包删除字段

create table T_TEST_COL_3as select ids,dates,vara,varb,varc,vard  from t_test_col_2;create table T_TEST_COL_mid(  ids   NUMBER,  dates DATE,  vara  VARCHAR2(2000),  varb  VARCHAR2(2000),  varc  VARCHAR2(2000));BEGIN   DBMS_REDEFINITION.CAN_REDEF_TABLE('ENMOTEST','T_TEST_COL_3', DBMS_REDEFINITION.CONS_USE_ROWID);END;/BEGIN   DBMS_REDEFINITION.START_REDEF_TABLE(         uname => 'ENMOTEST',         orig_table => 'T_TEST_COL_3',         int_table => 'T_TEST_COL_MID',         col_mapping => 'IDS IDS, DATES DATES, VARA VARA,VARB VARB,VARC VARC',         options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);END;/DECLARE    error_count pls_integer := 0;BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ENMOTEST',                   'T_TEST_COL_3',                   'T_TEST_COL_MID',                    dbms_redefinition.cons_orig_params ,                   TRUE,                   TRUE,                   TRUE,                   FALSE,                   error_count);    DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));END;/BEGIN dbms_redefinition.finish_redef_table('ENMOTEST','T_TEST_COL_3','T_TEST_COL_MID');  END;DROP TABLE T_TEST_COL_MID;

影响范围：

1. 中间表的大小与原表相当(需要耗费很大的空间及产生大量归档日志)。

2. 先阻塞insert，再阻塞select，时间一秒多，等待事件中能看到只有非常短暂的TM锁表操作。