oracle在线分区,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','yyyy-mm-ddhh24:mi:ss')),

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', 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;

--最后查询数据

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