生产库环境:
Oracle版本 11.2.0.3.0 + ASM
大表数据量为近3千万,分区键为日期字段
流程:
1. 停止中间件应用系统
2. 利用CTAS +nologging+并行来处理,这样速度会提高许多
CREATE TABLE t_tab_new nologging parallel(degree 2) partition by range (BALDATE)
interval(numtoyminterval(1,'MONTH'))
(partition PART_1211 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))) AS SELECT * FROM t_tab ;
注: 由于使用了11g自动分区特性,应当注意两点,一个必须至少要定义一个分区,而且日期必须是1号到28号之间,如果你这样写就会报错:
TO_DATE(' 2012-11-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
3. 重命名相关表
alter table t_tab rename to t_tab_old;
alter table t_tab_new rename to t_tab;
4. 将触发器、相关索引等转移到新建的表上来
重新在新表上创建索引及触发器,注意索引名不要和原来的重复,并且大表创建索引要注意nologging+并行,如:
create index IDX_T_TAB01 on T_TAB (SHOPCODE) nologging parallel 4; --全局索引
---
总共在10分钟之内完成。
提示:操作前应注意用户表空间、temp表空间是否足够,索引创建期间可通过select * from v$session_longops where target='***' 来查询进度状态
--
下面是采用基于主键的在线重定义来将普通大表转为分区表:
declare
v_user varchar2(50):='NJSXBAK';
v_tab varchar2(50):='T_EXTERBILL';
v_tmptab varchar2(50):='T_EXTERBILL_TMP';
num_errors PLS_INTEGER;
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_user,v_tab,DBMS_REDEFINITION.CONS_USE_PK);
DBMS_REDEFINITION.START_REDEF_TABLE(v_user,v_tab,v_tmptab,null,dbms_redefinition.cons_use_pk);
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(v_user,v_tab,v_tmptab,DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
if num_errors>0 then
RAISE_APPLICATION_ERROR(-20001, 'ERRORS on COPY_TABLE_DEPENDENTS!');
end if;
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(v_user,v_tab,v_tmptab);
DBMS_REDEFINITION.FINISH_REDEF_TABLE(v_user,v_tab,v_tmptab);
end;