Oracle普通表->分区表转换(9亿数据量)

转载至http://www.cnblogs.com/jyzhao/p/4378753.html

背景介绍:

环境:Linux 5.5 + Oracle 10.2.0.4

某普通表T,由于前期设计不当没有分区,如今几年来的数据量已达9亿+, 空间占用大约350G,在线重定义为分区表不现实,故采取申请时间窗口停此表应用,改造为分区表。

若T表数据量适当,可选用在线重定义操作时,可参考:http://www.cnblogs.com/jyzhao/p/3876634.html

1.创建分区表

-- Create table 创建分区表T_PART,分区从14年6月开始。

1
2
3
4
5
6
7
8
create  table  T_PART
(
……
)
partition  by  range(time_stamp)(
   partition P20140601  values  less than (TO_DATE( ' 2014-06-01 00:00:00' 'SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN' ))
     tablespace DBS_D_JINGYU
);

使用分区添加工具添加到15年6月份。

2.设置新建分区表为nologging, 重命名原表T为T_OLD

1
2
3
alter  table  t_part nologging;
 
rename T  to  T_old;

3.并行直接路径插入

1
alter  session enable parallel dml;
1
2
3
insert  /*+ append parallel(p,10) */  into  t_part p  select  /*+ parallel(n,10) */  from  T_old n;
 
commit ;

查看下insert的执行计划,确定都能用到并行度。

1
explain plan  for  insert  /*+ append parallel(p,10) */  into  t_part p  select  /*+ parallel(n,10) */  from  T_old n;

执行插入脚本

1
2
3
4
5
6
7
8
9
10
11
SQL> @/home/oracle/ insert
 
~~~~~~~~~~~~~~~~~~~~~~~~~
 
已创建908792694行。
 
已用时间:  02: 09: 37.94
 
提交完成。
 
已用时间:  00: 08: 13.76

4.为分区表建立索引

4.1 重命名历史表的索引名

1
2
3
4
alter  index  PK_T rename  to  PK_T_bak;
alter  table  T_old rename  constraint  PK_T  to  PK_T_bak;
alter  index  IDX_T_2 rename  to  IDX_T_2_bak;
alter  index  IDX_T_3 rename  to  IDX_T_3_bak;

4.2 给新分区表T_PART创建主键及索引

1
2
3
4
5
6
create  unique  index  PK_T  on  T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID)  local  tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  04: 39: 53.10
alter  table  T_PART  add  constraint  PK_T  primary  key  (OID, TIME_STAMP, SERIAL_NO, CITY_ID);
表已更改。
已用时间:  00: 00: 00.43
1
2
3
4
5
6
create  index  IDX_T_2  on  T_PART (TIME_STAMP, SERIAL_NO, CITY_ID)  local  tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 27: 49.92
create  index  IDX_T_3  on  T_PART (TIME_STAMP, CITY_ID)  local  tablespace DBS_I_JINGYU nologging parallel 32;
索引已创建。
已用时间:  02: 19: 06.74

4.3 修改索引和表为logging,noparallel

1
2
3
4
alter  index  PK_T logging noparallel;
alter  index  IDX_T_2 logging noparallel;
alter  index  IDX_T_3 logging noparallel;
alter  table  T_PART logging;

4.4 遇到的问题

建立唯一性索引时报错:

1
2
3
4
5
6
7
SQL>  create  unique  index  PK_T  on  T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID)  local  tablespace dbs_i_jingyu nologging parallel 32;
 
create  unique  index  PK_T  on  T_PART(OID, TIME_STAMP, SERIAL_NO, CITY_ID)  local  tablespace dbs_i_jingyu nologging parallel 32
 
ORA-12801: 并行查询服务器 P000 中发出错误信号
 
ORA-01652: 无法通过 128 (在表空间 TMP 中) 扩展  temp 

解决方式:增加临时表空间大小

1
2
3
4
5
alter  tablespace TMP  add  tempfile  '/usr3/oradata2/sysdata/tmp02.dbf'  size  30G;
 
alter  tablespace TMP  add  tempfile  '/usr3/oradata2/sysdata/tmp03.dbf'  size  30G;
 
alter  tablespace TMP  add  tempfile  '/usr3/oradata2/sysdata/tmp04.dbf'  size  30G;

5.rename表,恢复T表的相关应用

rename T_PART为T,恢复T表应用。

1
rename T_PART  to  T;

根据实际情况决定是否彻底drop掉T_OLD,释放空间。

1
drop  table  T_OLD purge;

 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值