表分区维护的sql(三)
on table.';
Pro_Record_Log(v_comments, v_comments);
goto here;
end if;
--获取当前日期，判断是否需要做分区维护
select sysdate into v_sysdate from dual;
if (v_row.partitionflag = 1) then
--按天分区，肯定每天都要维护
v_partitionchar := Const_Partition_DAY_prefix;
select 'P' || to_char(v_sysdate - v_row.retention_period, 'dd')
into v_partition_name
from dual;
elsif (v_row.partitionflag = 2) then
--按周分区，肯定每天都要维护 www.2cto.com
v_partitionchar := Const_Partition_WEEK_prefix;
v_comments := 'partition_flag:' || v_row.partitionflag ||
' of maintenance table :' || v_row.table_name ||
' in partition_table can''t be supported.';
Pro_Record_Log(v_comments, v_comments);
goto here;
elsif (v_row.partitionflag = 3) then
--按月分区，每月1号维护
Select to_char(v_sysdate, 'dd') into v_day from dual;
begin
select to_number(param_value)
into v_MonthExecution_DAY
from partition_param t
where t.param_mask = 'MONTH_EXECUTION_DAY';
exception
when others then
if SQLCODE = -1403 then
v_comments := 'when maintenancing ' || v_row.table_name ||
',MONTH_EXECUTION_DAY in partition_param is invalid. it''s must be [1-28].';
Pro_Record_Log(v_comments, v_comments);
else
v_comments := 'when maintenancing ' || v_row.table_name ||
',an error happened.' || SQLERRM;
Pro_Record_Log(v_comments, v_comments);
end if;
goto here;
www.2cto.com
end;
if (v_MonthExecution_DAY is null or v_MonthExecution_DAY > 28 or
v_MonthExecution_DAY < 1) then
v_comments := 'when maintenancing ' || v_row.table_name ||
',MONTH_EXECUTION_DAY in partition_param is invalid. it''s must be [1-28].';
Pro_Record_Log(v_comments, v_comments);
goto here;
end if;
if (v_day = v_MonthExecution_DAY) then
v_partitionchar := Const_Partition_MONTH_prefix;
select 'P' || to_number(to_char(add_months(v_sysdate,
-v_row.retention_period),
'mm'))
into v_partition_name
from dual;
else
v_comments := 'it''s not a maintenance day..current day:' ||
v_day || ', maintenance day:' ||
v_MonthExecution_DAY || '. table ' ||
v_row.table_name || ', partitionflag:' ||
v_row.partitionflag || ',retention_period:' ||
v_row.retention_period;
Pro_Record_Log(v_comments, v_comments);
goto here;
end if;
else
v_comments := 'partition_flag:' || v_row.partitionflag ||
' of maintenance table :' || v_row.table_name ||
' in partition_table can''t be supported.';
Pro_Record_Log(v_comments, v_comments);
end if; www.2cto.com
v_comments := 'begin to maintenance table ' || v_row.table_name ||
', partitionflag:' || v_row.partitionflag ||
',retention_period:' || v_row.retention_period;
Pro_Record_Log(v_comments, v_commen