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

爱被打了一巴掌 2023-02-12 11:26 32阅读 0赞

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

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

《[新增字段的一点一滴技巧][Link 1]》

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

《[探寻大表删除字段慢的原因][Link 2]》

《[大表删除字段为何慢？][Link 3]》

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

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

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

墨天轮这篇文章，《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);
    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，一旦触发，同样会带来非常大的停机风险。

**近期的热文：**

《[了解阿克曼转向原理的作用][Link 5]》

《[《你就是孩子最好的玩具》学习笔记 - 第一章][-]》

《[登录缓慢的诡异问题][Link 6]》

《[不可不知的7个JDK命令][7_JDK]》

《[一个Full GC次数过多导致系统CPU 100%的案例排查][Full GC_CPU 100]》

《[Java GC的基础知识][Java GC]》

《[Linux下的^M困惑][Linux_M]》

《[Oracle相关提问的智慧技巧][Oracle]》

《[很久以前的一篇对初学Oracle建议的文章][Oracle 1]》

《[一次对linux系统无影响的python3环境搭建过程及思考][linux_python3]》

《[Oracle Cloud云端账号的注册过程][Oracle Cloud]》

《[PLSQL Developer几个可能的隐患][PLSQL Developer]》

《[从70万字SRE神作提炼出的7千字精华文章][70_SRE_7]》

《[从数据误删到全量恢复的惊险记录][Link 7]》

《[NUMBER长度的误解][NUMBER]》

《[decode函数再挖掘][decode]》

《[《decode函数的妙用》网友的两个问题解答][decode 1]》

《[decode函数的妙用][decode 2]》  
《[公众号600篇文章分类和索引][600]》

[Link 1]: https://blog.csdn.net/bisal/article/details/103626212
[Oracle_EDB]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81578dddf6de9b33b1630dc52d464cd565324ef4f46ca63fb523077bc7c36453fd548c623d&idx=1&mid=2247488126&scene=21&sn=7f076fbb210425d5de07969062e283b0#wechat_redirect
[Link 2]: https://blog.csdn.net/bisal/article/details/86635285
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8154fdddf6ddeb66c11764b10710c6f86434f96219f82d6637eb88597dd3f0b89333db8eda&idx=1&mid=2247487758&scene=21&sn=4e6696b62b6d19c4faf36a04266521e9#wechat_redirect
[alter table]: https://blog.csdn.net/bisal/article/details/49182025
[Link 4]: https://blog.csdn.net/bisal/article/details/53242867
[alter table 1]: https://blog.csdn.net/bisal/article/details/45418303
[Link 5]: https://blog.csdn.net/bisal/article/details/106232934
[-]: https://blog.csdn.net/bisal/article/details/106232933
[Link 6]: https://blog.csdn.net/bisal/article/details/106204880
[7_JDK]: https://blog.csdn.net/bisal/article/details/106132095
[Full GC_CPU 100]: https://blog.csdn.net/bisal/article/details/106110289
[Java GC]: https://blog.csdn.net/bisal/article/details/106066216
[Linux_M]: https://blog.csdn.net/bisal/article/details/106030314
[Oracle]: https://blog.csdn.net/bisal/article/details/106009871
[Oracle 1]: https://blog.csdn.net/bisal/article/details/105963320
[linux_python3]: https://blog.csdn.net/bisal/article/details/105828334
[Oracle Cloud]: https://blog.csdn.net/bisal/article/details/105852162
[PLSQL Developer]: https://blog.csdn.net/bisal/article/details/105803468
[70_SRE_7]: https://blog.csdn.net/bisal/article/details/105742900
[Link 7]: https://blog.csdn.net/bisal/article/details/105697127
[NUMBER]: https://blog.csdn.net/bisal/article/details/105548618
[decode]: https://blog.csdn.net/bisal/article/details/105671807
[decode 1]: https://blog.csdn.net/bisal/article/details/105445417
[decode 2]: https://blog.csdn.net/bisal/article/details/105424100
[600]: https://blog.csdn.net/bisal/article/details/104548761