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 ;
-- 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/