清空任务表无效数据

来源：互联网发布：百度地图js api 定位编辑：程序博客网时间：2024/06/06 19:50

在maximo运行过程中，工作流任务数据越来越多，导致每次进入系统都比较慢，为了解决这个问题，特意做如下过程，清空无用数据！

create or replace procedure clearwstask is

tbname varchar2(100);

strSQL varchar2(1000);

recordcount number(10);

ideletecount number(10);

begin

select count(*) into recordcount from wfassignment where assignstatus=’INACTIVE’;

–备份表数据

tbname:=to_char(sysdate,’yymmdd’);

strsql:=’create table wfassignment_’||tbname||’ as select * from wfassignment where assignstatus=”INACTIVE”’;

EXECUTE IMMEDIATE strsql;

–删除数据

loop

if recordcount<2000 then

ideletecount:=recordcount;

recordcount:=0;

else

recordcount:=recordcount-2000;

ideletecount:=2000;

end if;

strSQL:=’begin delete from wfassignment where assignstatus=”INACTIVE” and rownum<’||ideletecount;

strSQL:=strSQL||’;commit; end;’;

EXECUTE IMMEDIATE strSQL;

exit when recordcount=0;

end loop;

–得建索引

strSQL:=’alter index WFASSIGN_NDX1 rebuild’;

EXECUTE IMMEDIATE strSQL;

strSQL:=’alter index WFASSIGN_NDX2 rebuild’;

EXECUTE IMMEDIATE strSQL;

strSQL:=’alter index WFASSIGN_NDX3 rebuild’;

EXECUTE IMMEDIATE strSQL;

strSQL:=’alter index WFASSIGN_NDX4 rebuild’;

EXECUTE IMMEDIATE strSQL;

end clearwstask;