用途:适用于按日期进行分区
create or replace procedure split_partition
(
v_owner in varchar2,
v_table_name in varchar2 , --表名
v_max_part_name in varchar2 , --最大分区名
v_penultimate_part_name in varchar2, --倒数第二个分区名, 格式PYYYYMMDD, 按天分区
v_cnt_split_part in integer --分割分区的数量
)
is
v_cnt_all_part integer(4); --总的分区数量
v_day date; --倒数第二分区的日期
v_sql_string varchar2(1000);--分割分区的sql
v_day_part number(14); --新分区对应日期,和分区字段类型一致
v_day_part_name varchar2(30); --新分区名
cnt_part_name integer; --判断新分区是否存在
Begin
select count(p.partition_name) into v_cnt_all_part from all_tab_partitions p
where p.table_owner=v_owner
and p.table_name=v_table_name;
v_day := to_date(substr(v_penultimate_part_name,2,8),'YYYYMMDD');
if v_cnt_all_part < 1023 then
for i in 1..v_cnt_split_part loop
if v_cnt_all_part + i <= 1023 then
v_day_part := to_number(to_char(v_day+i+1,'YYYYMMDDHH24MISS'));
v_day_part_name := 'P'||to_char(v_day+i,'YYYYMMDD');
select count(*) into cnt_part_name from all_tab_partitions p where p.partition_name= v_day_part_name and p.table_owner=v_owner and p.table_name=v_table_name;
if (cnt_part_name >= 1) then
DBMS_OUTPUT.put_line('ERR: partition name is exists,exit.');
exit;
end if;
v_sql_string :='alter table '||v_owner||'.'||v_table_name||' split partition '||v_max_part_name||' at ('||v_day_part||') into (partition '||v_day_part_name||' tablespace oam,partition PMAX tablespace oam)';
--DBMS_OUTPUT.put_line(v_sql_string);
execute immediate v_sql_string;
else
DBMS_OUTPUT.put_line('exceeding max partition limits');
end if;
end loop;
else
DBMS_OUTPUT.put_line('exceeding max partition limits');
end if;
end;
/
阅读(1406) | 评论(0) | 转发(0) |