表justin本来按月分区，但是进入7月份，数据量暴涨，每天都有百万条之多，应开发要求，将其7月份及其以后的分区都改为按天分区
目前该表最大分区到8月1号
首先需要对7月份现有分区执行split操作，然后添加8月份以后的分区

执行split操作语法如下
alter table justin split partition p1107 at (to_date('2011-07-02','yyyy-mm-dd')) into (partition p110701,partition p1107);
该语句会使p1107和p110701上的索引全部失效,且生成大量redo

生成脚本
declare
v_date date;
v_cnt number := 0;
v_cur varchar2(5);
v_mon varchar2(5);--number := 0;
v_day varchar2(5);--number := 0;
begin
v_date := to_date('2011-07-01','yyyy-mm-dd');
for i in 1..30 loop
    v_cur := to_char(v_date,'dd');
    v_date := v_date + 1;
    v_mon := to_char(v_date,'mm');
    v_day := to_char(v_date,'dd');
    dbms_output.put_line('alter table justin split partition p1107 at (to_date(''2011-'||v_mon||'-'||v_day||''',''yyyy-mm-dd'')) into (partition p11'||v_mon||v_cur||',partition p1107);');
    dbms_output.put_line('exec dbms_lock.sleep(10);');
end loop;
end;
生成的脚本如下
alter table justin split partition p1107 at (to_date('2011-07-30','yyyy-mm-dd')) into (partition p110729,partition p1107);
exec dbms_lock.sleep(10);
alter table justin split partition p1107 at (to_date('2011-07-31','yyyy-mm-dd')) into (partition p110730,partition p1107);
exec dbms_lock.sleep(10);

执行完毕后需要重建索引
declare
begin
for i in (select index_name,table_name from user_indexes where status ='UNUSABLE') loop
    execute immediate 'alter index '||i.index_name||' rebuild online';
end loop;
for j in (select u.index_name,u.partition_name,ui.table_name from user_ind_partitions u,user_indexes ui where u.status ='UNUSABLE' and u.index_name = ui.index_name) loop
    execute immediate 'alter index '||j.index_name|| ' rebuild partition '||j.partition_name||' online';
end loop;
end;

该表到8月1号过期，为其新增一年的分区
declare
v_date date;
v_cnt number := 0;
v_year varchar2(2);
v_curday varchar2(2);
v_mon varchar2(2);--number := 0;
v_day varchar2(2);--number := 0;
begin
v_date := to_date('2011-08-01','yyyy-mm-dd');
for i in 1..365 loop
    v_year := to_char(v_date,'yy');
    v_curday := to_char(v_date,'dd');
    v_date := v_date + 1;
    v_mon := to_char(v_date,'mm');
    v_day := to_char(v_date,'dd');
    dbms_output.put_line('alter table justin add partition P'||v_year||v_mon||v_curday||' values less than (to_date(''20'||v_year||'-'||v_mon||'-'||v_day||''',''yyyy-mm-dd''));');-- into (partition p11'||v_mon||v_curday||',partition p1107);');
   end loop;
end;
输出结果如下
alter table justin add partition P110801 values less than (to_date('2011-08-02','yyyy-mm-dd'));
alter table justin add partition P110802 values less than (to_date('2011-08-03','yyyy-mm-dd'));
alter table justin add partition P110803 values less than (to_date('2011-08-04','yyyy-mm-dd'));

ITPub博客

使用split对分区表再分区

最新文章