没分区的大表的到分区表的轮换

1.创建一个与原来大表一样结构的表
-- Create table
create table MOD_MR_W_STATDATA_2D_D_BK
(
  start_time   DATE,
  ne_cell_id   VARCHAR2(20),
  data_type    NUMBER(2),
  group_type   NUMBER(2),
  city_id      NUMBER(5),
  perf_subtype NUMBER(12),
  totalnum     NUMBER(10),
  x_value      NUMBER(10),
  y0           NUMBER(10),
  y1           NUMBER(10),
  y2           NUMBER(10),
  y3           NUMBER(10),
  y4           NUMBER(10),
  y5           NUMBER(10),
  y6           NUMBER(10),
  y7           NUMBER(10),
  y8           NUMBER(10),
  y9           NUMBER(10),
  y10          NUMBER(10),
  y11          NUMBER(10),
  y12          NUMBER(10),
  y13          NUMBER(10),
  y14          NUMBER(10),
  y15          NUMBER(10),
  y16          NUMBER(10),
  y17          NUMBER(10),
  y18          NUMBER(10),
  y19          NUMBER(10),
  y20          NUMBER(10),
  y21          NUMBER(10),
  y22          NUMBER(10),
  y23          NUMBER(10)

)  tablespace WCDMA_MR
partition by range (start_time) interval(numtodsinterval(1,'day'))
(
   partition p1 values less than (to_date('2000-05-07 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace WCDMA_MR ,
   partition p2 values less than (to_date('2012-05-10 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace WCDMA_MR
);
/
-- 交换数据
alter table MOD_MR_W_STATDATA_2D_D_BK exchange partition p2 with table MOD_MR_W_STATDATA_2D_D ;
-- 改原表名 为WT_OD_MR_W_STATDATA_2D_D
alter table MOD_MR_W_STATDATA_2D_D RENAME TO WT_MOD_MR_W_STATDATA_2D_D ;
-- 改新表MOD_MR_W_STATDATA_2D_D_BK 为 MOD_MR_W_STATDATA_2D_D
alter table MOD_MR_W_STATDATA_2D_D_BK rename to MOD_MR_W_STATDATA_2D_D ;
-- drop table WT_MOD_MR_W_STATDATA_2D_D PURGE ;

把大表 改成 自动分区的 语句 ：
 ALTER TABLE MOD_MR_W_STATDATA_2D_D_BK SET INTERVAL(numtodsinterval(1,'DAY'));

一些对分区的操作命令：
alter table MOD_MR_W_STATDATA_2D_D_BK add partition p2
values less than (to_date('2012-05-10 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace WCDMA_MR ;
ALTER TABLE MOD_MR_W_STATDATA_2D_D_BK TRUNCATE PARTITION SYS_P177143 ;

alter table MOD_MR_W_STATDATA_2D_D_BK drop partition p2 ;
alter table MOD_MR_W_STATDATA_2D_D_BK truncate partition p2 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26953882/viewspace-723133/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26953882/viewspace-723133/