专栏首页bisal的个人杂货铺Oracle删除字段的方式和风险,你都了解么?

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

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);
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,一旦触发,同样会带来非常大的停机风险。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 《Oracle性能优化求生指南》-第四章:数据库逻辑设计和物理设计-学习小结-1

    1、建立逻辑数据模型为第一阶段,包括对应用程序需要处理和存储的信息进行建模,并确保所有必要的数据都能够正确、完整且无歧义地表示。在关系数据库的实现中,这通常是指...

    bisal
  • 并行创建主键的问题延伸

    《使用并行创建主键约束的“奇葩”过程》这篇文章发到技术讨论群,得到了很多朋友的建议和反馈,更深层次地对问题进行了理解。

    bisal
  • 使用JBoss Tool反向创建PO类

    使用Hibernate Tool可以根据已有的数据库结构,反向创建PO、DAO等类,方便于开发,最近正好有个工作内容,需要用这个,所以重新捡起来学习下。

    bisal
  • 分布式文件系统Mogilefs介绍

    一、分布式文件系统简介: 什么是分布式存储: 分布式存储系统,是将数据分散存储在多台独立的设备上。传统的网络存储系统采用集中的存储服务器存放所有数据,存储服务器...

    小小科
  • 0307前端笔试题

    2.制作级联菜单的时候,调用的是下拉列表的哪个事件? 解答: onChange()

    治电小白菜
  • 契约测试:解决微服务测试问题的一种手段

    契约测试(ContractTest)第一次看到我是在Martin Fowler的文章里。(原文在这里感兴趣的可以去看看https://martinfowler....

    Criss@陈磊
  • [语音识别] 05 基于GMM-HMM的语音识别系统(单音素、三音素)

    MachineLP
  • Float 的那些事

      display:inline-block某种意义上的作用就是包裹(wrap),而浮动也有类似的效果。举个常见例子,或许您有实现宽度自适应按钮的经验,实现宽度...

    超然
  • TensorFlow 2.0 识别MNIST手写数字

    用户6021899
  • HTML5笔记

    菜鸟雷

扫码关注云+社区

领取腾讯云代金券