普通大表转为分区表

生产库环境:

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;