天天看點

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,一旦觸發,同樣會帶來非常大的停機風險。