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

--复制一份要分区的表
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',partition SMS_SENDING_PARTOTION_OTHER values less THAN (MAXVALUE)  
);
--中间表创建主键
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','yunxin_notice',null,'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_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_test');
end;

--最后查询数据
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
oracle在线重定义分区按照时间

猜你在找的Oracle相关文章
