自動SPLIT ORACLE PARTITION

create or replace procedure pro_split_subpart(v_tab varchar2) as
cursor c_subp(v_1 varchar2) is
select *
from user_tab_subpartitions
where table_name = upper(v_1)
and subpartition_name like '%1106%';
v_sql varchar2(4000);
v_p_date varchar2(20);
v_p_prex varchar2(200);
v_p_date2 date;
v_p_date3 date;
v_i number;

v_sql2 varchar2(2000);
v_sql3 varchar2(2000);
v_sql4 varchar2(2000);
begin
for a in c_subp(v_tab) loop
v_sql := 'alter table ' || a.table_name || ' split subpartition ' ||
a.subpartition_name || ' at ';
--分割槽字首
v_p_prex := substr(a.subpartition_name,
1,
instr(a.subpartition_name, '_', 1, 2) );
--分割槽日期
v_P_date := substr(a.subpartition_name,
instr(a.subpartition_name, '_', 1, 2) + 1,
4);
--分割槽時間
v_p_date2 := to_date(v_p_date, 'yymm');
v_i := add_months(v_p_date2, 1) - v_p_date2;
v_p_date2 := add_months(v_p_date2, -1);
for b in 1 .. v_i-1 loop
--(TO_DATE('2009-01-05','YYYY-MM-DD') )
v_sql2 := 'to_date(''' ||
to_char(v_p_date2 + b, 'yyyymmdd') || ''',''yyyymmdd'')';
v_sql3 := 'subpartition ' ||v_p_prex || to_char(v_p_date2 + b, 'yymmdd');

v_sql4 := v_sql || ' ( '||v_sql2||' ) ' || ' into ('||v_sql3||',subpartition '||a.subpartition_name||' ) ';
--dbms_output.put_line(v_sql4);
begin
execute immediate v_sql4;
exception when others then
null;
end;
end loop;
-- dbms_output.put_line(v_sql);
-- dbms_output.put_line(v_sql2);
-- dbms_output.put_line(v_sql3);
v_sql2:='';
v_sql3:='';
v_sql:='';
end loop;

end;

[@more@]