表分区维护的sql(5)
来源：未知 责任编辑：责任编辑 发表时间:2015-10-08 14:16 点击:次
sysdate,
substr(v_operation, 1, 255),
substr(v_comments, 1, 4000));
COMMIT;
END;
procedure pro_maintenance as
V_ROW PARTITION_TABLE%rowtype;
TYPE Tcur IS REF CURSOR;
cur_partitiontable Tcur;
v_sql varchar2(4000);
v_comments varchar2(255);
cnt number;
v_sysdate date;
v_partitionchar varchar2(10);
v_day number;
v_rtn number;
v_partition_name varchar2(10);
v_MonthExecution_DAY number;
begin
v_sql := 'select * from partition_table where valid_flag=1';
OPEN cur_partitiontable FOR v_sql; www.2cto.com
loop
<<here>>
fetch cur_partitiontable
into V_ROW;
exit when cur_partitiontable%notfound;
select count(1)
into cnt
from user_tab_partitions
where upper(table_name) = upper(v_row.table_name);
if (cnt = 0) then
v_comments := v_row.table_name ||
' defined in partition_table is not a partition 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
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
