oracle在线重定义分区按照时间

--复制一份要分区的表
create TABLE sms_sending_2017_08_30 AS SELECT * FROM sms_sending;
 
--建立主键
alter table sms_sending_2017_08_30 add constraint pk_ss_id primary key (SMS_SENDING_ID);

--检查新建的表是否可以在线重定义
begin dbms_redefinition.can_redef_table('sms_two', 'sms_sending_2017_08_30');
end;

--创建中间表并按照时间分区
create table SMS_SENDING_test
(
  SMS_SENDING_ID  VARCHAR2(32) not null,
  SMS_TEMPLATE_ID VARCHAR2(32),
  SEND_TYPE       VARCHAR2(10) not null,
  CREATE_DATE     DATE not null,
  STS             VARCHAR2(1) not null,
  STS_DATE        DATE not null,
  CREATE_ID       VARCHAR2(32) not null,
  CREATE_NAME     VARCHAR2(100),
  SEND_BEGIN_DATE DATE,
  SEND_END_DATE   DATE,
  SMS_ACCOUNT_ID  VARCHAR2(32) not null,
  DETAIL_SUM      VARCHAR2(10)
) partition by range(CREATE_DATE)(  
          partition SMS_SENDING_PARTOTION_02 values less than (TO_DATE('2017-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')),  
          partition SMS_SENDING_PARTOTION_03 values less than (TO_DATE('2018-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')),  --16年数据
          partition SMS_SENDING_PARTOTION_OTHER values less THAN (MAXVALUE) --17年数据
);
--中间表创建主键
alter table SMS_SENDING_test add constraint pk_sst_id primary key (SMS_SENDING_ID);
--启动在线重定义
begin dbms_redefinition.start_redef_table('sms_two', 'sms_sending_2017_08_30', 'SMS_SENDING_test');
end;

--查询中间表是否存入原表数据
select * from SMS_SENDING_test;

--在重定义中原表中的数据可能发生更改模拟原表数据更改
insert into sms_sending_2017_08_30 (SMS_SENDING_ID, SMS_TEMPLATE_ID, SEND_TYPE, CREATE_DATE, STS, STS_DATE, CREATE_ID, CREATE_NAME, SEND_BEGIN_DATE, SEND_END_DATE, SMS_ACCOUNT_ID, DETAIL_SUM)
values ('78b612e12e1942b4a51667495718a156', '2016', 'notice', to_date('30-08-2017 16:25:26', 'dd-mm-yyyy hh24:mi:ss'), 'A', to_date('30-08-2017 16:25:26', 'dd-mm-yyyy hh24:mi:ss'), 'yunxin_notice', 'yunxin_notice', null, null, 'yunxin_notice', '1');
commit;

--查看两张表中数据相差一条上述插入的数据
select * from sms_sending_2017_08_30 t order by t.create_date desc;
select * from SMS_SENDING_test t order by t.create_date desc;

--从原表中同步数据到中间表
begin dbms_redefinition.sync_interim_table('sms_two', 'sms_sending_2017_08_30', 'SMS_SENDING_test');
end;

--检查两张表是否同步
select * from sms_sending_2017_08_30 t order by t.create_date desc;
select * from SMS_SENDING_test t order by t.create_date desc;

--结束在线重定义
begin dbms_redefinition.finish_redef_table('sms_two', 'sms_sending_2017_08_30', 'SMS_SENDING_test');
end;

--最后查询当前表所有的分区 注意oracle表名区分大小写
select table_name, partition_name from user_tab_partitions where table_name = 'SMS_SENDING_2017_08_30';
select * from sms_sending_2017_08_30 partition(SMS_SENDING_PARTOTION_OTHER);--查询指定分区数据

在删除分区的过程中千万要小心误删数据啊

本文参考文章

http://blog.csdn.net/jameshadoop/article/details/48105297