[TOC]
Oracle之大表DDL操作那些事
场景：万级以上数据量在给表增加字段的时候，可以随意执行Alter吗？
下面我们来看这条Alter语句：
- 给表增加字段并设置默认值
alter table table_name add (text_type NUMBER(1) default 0 NOT NULL);
DDL原理：这条DDL执行，其实是先给表增加一列（不设置默认值及NOT NULL），然后给执行全表的update，更新这个字段为默认值，最终在设置这个字段的默认值及非空。
如果各位有大数据量的表，可以使用表备份语句建立备份表，并进行上方这条DDL测试，Alter期间可以通过锁相关查询语句，看看产生了几级的锁。
-- 建表结构+复制数据 create table newtable as select * from oldtable; --查出oracle当前的被锁对象 SELECT l.session_id sid, l.locked_mode 锁模式, l.oracle_username 登录用户, l.os_user_name 登录机器用户名, s.machine 机器名, s.terminal 终端用户名, o.object_name 被锁对象名, s.logon_time 登录数据库时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid;
很明显，全表的Update肯定会产生行锁也就是3级RX锁，这样数据量越大，处理时间变长，事务也越大。业务在处理DML语句时，会因目标行被这里的操作锁住而产生阻塞等待，那么应用程序也就阻塞了，相当于人工手动停机，这样的事肯定没有一个人愿意看见，那么怎么处理合适呢？
方案一
首先，分析原因，造成锁表的原因是全表更新，产生的大事务导致，那么我们如果把事务拆解下来，分割一个个的小事务，这样就可以有效的规避全表的行锁了，业务应用层也不会产生阻塞。
既然有了思路，就搞起来。
整体的思路是这样的：
st=>start: start
o1=>operation: 增加可空列
o2=>operation: 给可空列增加默认值
o3=>operation: 拆分若干个小事务更新
o4=>operation: 修改列非空
end=>end: end
st->o1->o2->o3->o4->end
对应Sql，其中err_log用来记录错误信息，以供后续分析，如果执行没问题，记得删除此表。
-- start
-- 错误日志表-临时
create table ins.err_log_20190826(status varchar2(200));
-- 增加可空列
alter table ins.target_table add target_column NUMBER(2);
-- 给可空列增加默认值
-- 注意：这一操作是为了防止新数据生成，新字段值为null，这样游标执行无法彻底更新完所有null值，导致最后执行出错
alter table ins.target_table modify target_column default 0;
-- 拆分若干个小事务更新：其中5000这个值源于实际测试得出，每5000提交一次事务，效率较高，实际中，各自可以自行测试
declare
v_cnt pls_integer;
begin
v_cnt:=1;
while v_cnt>0 loop
update ins.target_table t set t.target_column = 0 where t.target_column is null and rownum<=5000;
v_cnt:=sql%rowcount;
commit;
end loop;
end;
-- 修改列非空
alter table ins.target_table modify target_column not null;
其中上述游标语句也可以写出如下：
declare
n1 number :=0;
v_str varchar2(200);
begin
for i in (select target_id from ins.target_table)
loop
n1 := n1+1;
update ins.target_table set target_column = 0 where target_id = i.target_id;
if mod(n1,5000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
rollback;
v_str := 'target_table' || SQLCODE || '_' || SQLERRM;
insert into err_log_20190826 (status) values(v_str);
commit;
end;
好，到此差不多已经讲完了这个问题，但是平常工作中，我们在没有必要给这个字段加上非空要求时，就可以采用以下的sql，来增加新列，也就不会造成上述说的问题。是因为修改列增加默认值，只会对新插入的数据生效，而历史的数据，是不会处理的。
alter table table_name add (text_type NUMBER(1));
alter table table_name modify text_type default 0;
方案二
oracle10G及以上可以使用在线重定义的方式进行。
官方文档：点击进入
Oracle提供了常量供大家使用：
dbms_redefinition.cons_use_pk CONSTANT PLS_INTEGER := 1;
dbms_redefinition.cons_use_rowid CONSTANT PLS_INTEGER := 2;
权限要求
grant create any table to 用户;
grant alter any table to 用户;
grant drop any table to 用户;
grant lock any table to 用户;
grant select any table to 用户;
grant create any trigger to 用户;
grant create any index to 用户;
在线重定义的思路：
st=>start: start
o1=>operation: 选择判断在线重定义模式
o2=>operation: 建立中间表
o3=>operation: 开始重定义
o4=>operation: 给中间表建立索引等约束
o5=>operation: 锁定同步数据-可选
o6=>operation: 结束重定义
end=>end: end
st->o1->o2->o3->o4->o5->o6->end
原理：通过对中间表（即想要修改原表的最终结构）执行大量DML复制原表的数据，实现原表的零锁表，以达到不影响用户的操作。最终在结束重定义前，对中间表按照原表建立一套索引等（刚开始建立中间表时，不建议建立索引，会增加重定义的时间，每次DML都需要维护索引），以上都结束后，执行结束重定义，这一步的内部过程会再次同步下数据（即执行锁定同步数据，会锁表，由于数据较少，锁定时间不会太长），数据同步结束后，将表名互换。
实现步骤：
原表DDL：
CREATE TABLE INS.test_table ( id NUMBER(10) NOT NULL, link_id NUMBER(10) NOT NULL, link_TYPE NUMBER(2) NOT NULL, CREATE_ID NUMBER(10), CREATE_TIME TIMESTAMP(6) DEFAULT sysdate, MODIFY_ID NUMBER(10) , MODIFY_TIME TIMESTAMP(6) DEFAULT sysdate, CONSTRAINT PK_test_table PRIMARY KEY (id) ); create index INS.IDX_test_table_1 on ins.test_table (link_id); create index INS.IDX_test_table_2 on ins.test_table (link_TYPE);需要给原表增加 flag 字段
alter table ins.test_table add (flag NUMBER(1) DEFAULT 0 NOT NULL);
-
决定重定义模式
-- 通过主键是否可以重定义 begin DBMS_REDEFINITION.can_redef_table('INS','test_table'); end;默认是主键模式重定义，如果未报错，则可以使用主键模式，否则使用ROWID模式重定义（取决于原表是否有主键）
-- 通过ROWID是否可以重定义 begin DBMS_REDEFINITION.can_redef_table('INS','test_table', dbms_redefinition.cons_use_rowid); end; -
建立中间表
CREATE TABLE INS.test_table_temp ( id NUMBER(10) NOT NULL, link_id NUMBER(10) NOT NULL, link_TYPE NUMBER(2) NOT NULL, flag NUMBER(1) DEFAULT 0 NOT NULL, CREATE_ID NUMBER(10), CREATE_TIME TIMESTAMP(6) DEFAULT sysdate, MODIFY_ID NUMBER(10) , MODIFY_TIME TIMESTAMP(6) DEFAULT sysdate ); -
开始重定义
-- 重定义 begin DBMS_REDEFINITION.START_REDEF_TABLE('INS','test_table','test_table_temp',null,dbms_redefinition.cons_use_pk); end; ---------需要注意这里的输入参数选择 PROCEDUREstart_redef_table(uname IN VARCHAR2,----用户名 orig_table INVARCHAR2,----源表名 int_table INVARCHAR2,----中间表名 col_mapping IN VARCHAR2 :=NULL,---源表和中间表列之间的映射，map； options_flag IN BINARY_INTEGER := 1,---重定义方式，这里的 1 代表主键模式 2 代表ROWID模式 (可以直接使用oracle提供的常量) orderby_cols IN VARCHAR2 := NULL,---对于分区表重定义的时候，分区列名 part_name INVARCHAR2 :=NULL);---对于分区表重定义的时候，需要重定义的分区。其中最后2个参数没用到，因为这里是由普通表转换为分区表； -
给中间表建立索引等约束
-- 由于索引名称是全局的，所以这里的命名肯定不能与原表冲突，如果必须要改为原表表名的可以在结束重定义后，删除中间表，再次修改索引名称 create index INS.IDX_test_table_temp_1 on ins.test_table_temp (link_id); create index INS.IDX_test_table_temp_2 on ins.test_table_temp (link_TYPE); -
锁定同步数据（可选）
-- 多次同步数据，可以减少结束重定义时的等待时间 begin DBMS_REDEFINITION.sync_interim_table('INS','test_table','test_table_temp'); end; -
结束重定义
-- 结束重定义 begin DBMS_REDEFINITION.FINISH_REDEF_TABLE('INS','test_table','test_table_temp'); end; -
删除中间表
-- 此时原表的结构已经是我们想要的结构，中间表是原表的初始结构 drop table ins.test_table_temp -
（可选）重命名索引、触发器和约束 对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$); ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
注意：如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE() 过程。