/*******************************************************
author:zuozewei
time :2018/01/24
功 能:清理表数据,保留z_ddwpt表当天数据,保留z_his表三天数据,删除后要检查索引的状态
UNUSABLE后重建索引,以达到对表的分析
说 明:通过JOB任务每天晚上0点执行一次,表达式'TRUNC(SYSDATE + 1)'
eg:exec deletetable_proc();
********************************************************/
create or replace procedure deletetable_proc is
v_sql_1 varchar2(2000); --回收空间碎片
v_tablename varchar2(10); --表名
---为索引失效而重建索引定义游标
cursor cursor_idx is
select index_name
from user_indexes
where table_name = UPPER(v_tablename)
and status = 'UNUSABLE';
record_cursor_idx cursor_idx%rowtype;
begin
v_tablename := 'Z_HIS';
delete z_his t where t.gathertime < trunc(sysdate - 3, 'dd');
commit;
v_sql_1 := 'ALTER TABLE z_his move';
execute immediate v_sql_1;
delete z_ddwpt t where t.gathertime < trunc(sysdate - 1, 'dd');
commit;
--重建失效的索引
open cursor_idx;
loop
fetch cursor_idx
into record_cursor_idx;
exit when cursor_idx%notfound;
execute immediate 'alter index ' || record_cursor_idx.index_name ||' rebuild';
end loop;
close cursor_idx;
end deletetable_proc;
配合Job定时调用