Oracle中和字段相关的知识还是很多的,不要小瞧了字段的增删改,一个小小的字段操作,一旦不清楚他的原理,随意在生产环境中执行,就可能产生让你印象深刻的影响。
一些和字段操作相关的历史文章,
《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;
运行业务模拟程序,开始正常插入日志,然后删除大表的字段。
alter table t_test_col drop column vard;
影响范围:
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表空间会全部耗尽。为了解决这个问题,有了第三种场景。
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。
执行结果是:
换成普通分区表,先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;
影响范围:
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;
影响范围:
在场景一到场景三的执行过程中,突然中断会话,观察中断后的情况.