Oracle中drop_column的幾種方式和風險

墨墨導讀：你是否足夠了解在生産環境上執行drop column操作或者類似的DDL操作有多危險？

生産環境的一張大表上執行了一個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);
begin
for 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_3
as 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;
BEGIN
DBMS_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鎖表操作。

場景五: 中斷測試

在場景一到場景三的執行過程中，突然中斷會話，觀察中斷後的情況：

1. 直接drop column，中斷後表可正常使用，字段仍然還在；
2. 先set unused，再drop unused columns，字段set之後就查不到了，中斷後，表可正常使用；
3. 先set unused，再drop unused columns checkpoint，中斷後，insert和select均報ORA-12986錯誤，提示必須執行alter table drop columns continue操作，其他操作不允許。

測試總結:

1. 在生産環境執行drop column是很危險的，如果是重要的或資料量很大的表，最好申請計劃停機時間視窗進行維護。
2. drop unused columns checkpoint雖然能解決復原段占用過高的問題，但是會帶來不可回退的風險。如果是非常大的表，隻能讓他跑完，但在跑的過程中，所有操作無法進行，這将會造成非常長時間的業務中斷。
3. 業務壓力不大的系統可采用dbms_redefinition線上重定義操作，隻會在finish那一步出現很短時間的阻塞。
4. 間隔分區上執行drop unused columns checkpoint存在bug，一旦觸發，同樣會帶來非常大的停機風險。