Jbpm 流程的清

项目运行5年了，Jbpm4 流程里的数据太多了，该如何删除那？

分析了一下，发现数据量比较大的jbpm表:
JBPM_BYTEARRAY 535750//流程变量实例表，外键表：JBPM_BYTEBLOCK，JBPM_VARIABLEINSTANCE
JBPM_BYTEBLOCK 536785//流程变量实例表，外键表：无
JBPM_MODULEINSTANCE 223072 外键表：JBPM_SWIMLANEINSTANCE，JBPM_TASKINSTANCE，JBPM_TOKENVARIABLEMAP
JBPM_POOLEDACTOR 494171 外键表：JBPM_TASKACTORPOOL
JBPM_PROCESSINSTANCE 111536 外键表：JBPM_RUNTIMEACTION，JBPM_VARIABLEINSTANCE，JBPM_TOKEN，JBPM_TOKEN，JBPM_TIMER，JBPM_MODULEINSTANCE 表里引用其他表的外键： 无

JBPM_SWIMLANEINSTANCE 306947 外键表：JBPM_POOLEDACTOR，JBPM_TASKINSTANCE
JBPM_TASKACTORPOOL 501204 外键表：无
JBPM_TASKINSTANCE 399973 外键表：JBPM_TASKACTORPOOL，JBPM_COMMENT，JBPM_VARIABLEINSTANCE，JBPM_MESSAGE，JBPM_TIMER
JBPM_TOKEN 111536 外键表：JBPM_TOKENVARIABLEMAP，JBPM_TASKINSTANCE，JBPM_PROCESSINSTANCE，JBPM_PROCESSINSTANCE， JBPM_VARIABLEINSTANCE，JBPM_TOKEN
JBPM_TOKENVARIABLEMAP 111520 外键表：JBPM_VARIABLEINSTANCE
JBPM_VARIABLEINSTANCE 1118740 外键表：无

没有在清空数据的表
JBPM_COMMENT 无数据
JBPM_RUNTIMEACTION 无数据
JBPM_TIMER 无数据
JBPM_MESSAGE 无数据

 
1.先删除日志表
truncate table JBPM_LOG;
2.把所有删除数据的表备份

create table JBPM_PROCESSINSTANCE2013 nologging as select * from JBPM_PROCESSINSTANCE;
commit;
create table JBPM_MODULEINSTANCE2013 nologging as select * from JBPM_MODULEINSTANCE;
commit;
create table JBPM_TOKEN2013 nologging as select * from JBPM_TOKEN;
commit;
create table JBPM_TOKENVARIABLEMAP2013 nologging as select * from JBPM_TOKENVARIABLEMAP;
commit;
create table JBPM_SWIMLANEINSTANCE2013 nologging as select * from JBPM_SWIMLANEINSTANCE;
commit;
create table JBPM_POOLEDACTOR2013 nologging as select * from JBPM_POOLEDACTOR;
commit;
create table JBPM_TASKACTORPOOL2013 nologging as select * from JBPM_TASKACTORPOOL;
commit;
create table JBPM_TASKINSTANCE2013 nologging as select * from JBPM_TASKINSTANCE;
commit;
create table JBPM_VARIABLEINSTANCE2013 nologging as select * from JBPM_VARIABLEINSTANCE;
commit;
create table JBPM_BYTEARRAY2013 nologging as select * from JBPM_BYTEARRAY;
commit;
create table JBPM_BYTEBLOCK2013 nologging as select * from JBPM_BYTEBLOCK;
commit;
3.清空所有已备份的表
alter table JBPM_BYTEARRAY disable primary key cascade;
alter table JBPM_BYTEBLOCK disable primary key cascade;
alter table JBPM_MODULEINSTANCE disable primary key cascade;
alter table JBPM_POOLEDACTOR disable primary key cascade;
alter table JBPM_PROCESSINSTANCE disable primary key cascade;
alter table JBPM_SWIMLANEINSTANCE disable primary key cascade;
alter table JBPM_TASKACTORPOOL disable primary key cascade;
alter table JBPM_TASKINSTANCE disable primary key cascade;
alter table JBPM_TOKEN disable primary key cascade;
alter table JBPM_TOKENVARIABLEMAP disable primary key cascade;
alter table JBPM_VARIABLEINSTANCE disable primary key cascade;
truncate table JBPM_BYTEARRAY;
commit;
truncate table JBPM_BYTEBLOCK;
commit;
truncate table JBPM_MODULEINSTANCE;
commit;
truncate table JBPM_POOLEDACTOR;
commit;
truncate table JBPM_PROCESSINSTANCE;
commit;
truncate table JBPM_SWIMLANEINSTANCE;
commit;
truncate table JBPM_TASKACTORPOOL ;
commit;
truncate table JBPM_TASKINSTANCE;
commit;
truncate table JBPM_TOKEN ;
commit;
truncate table JBPM_TOKENVARIABLEMAP;
commit;
truncate table JBPM_VARIABLEINSTANCE;
commit;
alter table JBPM_BYTEARRAY enable primary key;
alter table JBPM_BYTEBLOCK enable primary key;
alter table JBPM_MODULEINSTANCE enable primary key;
alter table JBPM_POOLEDACTOR enable primary key;
alter table JBPM_PROCESSINSTANCE enable primary key;
alter table JBPM_SWIMLANEINSTANCE enable primary key;
alter table JBPM_TASKACTORPOOL enable primary key;
alter table JBPM_TASKINSTANCE enable primary key;
alter table JBPM_TOKEN enable primary key;
alter table JBPM_TOKENVARIABLEMAP enable primary key;
alter table JBPM_VARIABLEINSTANCE enable primary key;
4.把需要保留的数据重新插入清空的表中
alter table JBPM_PROCESSINSTANCE disable all triggers;
insert /*append*/ into JBPM_PROCESSINSTANCE select * from JBPM_PROCESSINSTANCE2013 where to_char(START_,’yyyy-mm-dd’)>’2011-12-31′;
commit;
alter table JBPM_MODULEINSTANCE disable all triggers;
insert /*append*/ into JBPM_MODULEINSTANCE select * from JBPM_MODULEINSTANCE2013 where PROCESSINSTANCE_ in(select ID_ from JBPM_PROCESSINSTANCE);
commit;
alter table JBPM_TOKEN disable all triggers;
insert /*append*/ into JBPM_TOKEN select * from JBPM_TOKEN2013 where PROCESSINSTANCE_ in(select ID_ from JBPM_PROCESSINSTANCE);
insert /*append*/ into JBPM_TOKEN select * from JBPM_TOKEN2013 where SUBPROCESSINSTANCE_ in(select ID_ from JBPM_PROCESSINSTANCE);
commit;
insert /*append*/ into JBPM_TOKEN select * from JBPM_TOKEN2013 where ID_ in(select ROOTTOKEN_ from JBPM_PROCESSINSTANCE) and ID_ not in (select ID_ from JBPM_TOKEN);
insert /*append*/ into JBPM_TOKEN select * from JBPM_TOKEN2013 where ID_ in(select SUPERPROCESSTOKEN_ from JBPM_PROCESSINSTANCE)and ID_ not in (select ID_ from JBPM_TOKEN);
commit;
alter table JBPM_TOKENVARIABLEMAP disable all triggers;
insert /*append*/ into JBPM_TOKENVARIABLEMAP select * from JBPM_TOKENVARIABLEMAP2013 where CONTEXTINSTANCE_ in(select ID_ from JBPM_MODULEINSTANCE);
commit;
insert /*append*/ into JBPM_TOKENVARIABLEMAP select * from JBPM_TOKENVARIABLEMAP2013 where TOKEN_ in(select ID_ from JBPM_TOKEN) and ID_ not in(select ID_ from JBPM_TOKENVARIABLEMAP);
commit;
alter table JBPM_SWIMLANEINSTANCE disable all triggers;
insert /*append*/ into JBPM_SWIMLANEINSTANCE select * from JBPM_SWIMLANEINSTANCE2013 where TASKMGMTINSTANCE_ in(select ID_ from JBPM_MODULEINSTANCE);
commit;
alter table JBPM_POOLEDACTOR disable all triggers;
insert /*append*/ into JBPM_POOLEDACTOR select * from JBPM_POOLEDACTOR2013 where SWIMLANEINSTANCE_ in(select ID_ from JBPM_SWIMLANEINSTANCE);
commit;
alter table JBPM_TASKACTORPOOL disable all triggers;
insert /*append*/ into JBPM_TASKACTORPOOL select * from JBPM_TASKACTORPOOL2013 where POOLEDACTOR_ in(select ID_ from JBPM_POOLEDACTOR);
commit;
alter table JBPM_TASKINSTANCE disable all triggers;
insert /*append*/ into JBPM_TASKINSTANCE select * from JBPM_TASKINSTANCE2013 where TASKMGMTINSTANCE_ in(select ID_ from JBPM_MODULEINSTANCE);
commit;
insert /*append*/ into JBPM_TASKINSTANCE select * from JBPM_TASKINSTANCE2013 where TOKEN_ in(select ID_ from JBPM_TOKEN)and ID_ not in (select ID_ from JBPM_TASKINSTANCE);
commit;
insert /*append*/ into JBPM_TASKINSTANCE select * from JBPM_TASKINSTANCE2013 where SWIMLANINSTANCE_ in(select ID_ from JBPM_SWIMLANEINSTANCE)and ID_ not in (select ID_ from JBPM_TASKINSTANCE);
commit;
alter table JBPM_VARIABLEINSTANCE disable all triggers;
insert /*append*/ into JBPM_VARIABLEINSTANCE select * from JBPM_VARIABLEINSTANCE2013 where PROCESSINSTANCE_ in(select ID_ from JBPM_PROCESSINSTANCE);
commit;
insert /*append*/ into JBPM_VARIABLEINSTANCE select * from JBPM_VARIABLEINSTANCE2013 where TOKEN_ in(select ID_ from JBPM_TOKEN) and ID_ not in (select ID_ from JBPM_VARIABLEINSTANCE);
commit;
insert /*append*/ into JBPM_VARIABLEINSTANCE select * from JBPM_VARIABLEINSTANCE2013 where TOKENVARIABLEMAP_ in(select ID_ from JBPM_TOKENVARIABLEMAP) and ID_ not in (select ID_ from JBPM_VARIABLEINSTANCE);
commit;
insert /*append*/ into JBPM_VARIABLEINSTANCE select * from JBPM_VARIABLEINSTANCE2013 where TASKINSTANCE_ in(select ID_ from JBPM_TASKINSTANCE) and ID_ not in (select ID_ from JBPM_VARIABLEINSTANCE);
commit;
insert /*append*/ into JBPM_VARIABLEINSTANCE select * from JBPM_VARIABLEINSTANCE2013 where BYTEARRAYVALUE_ in(select ID_ from JBPM_BYTEARRAY) and ID_ not in (select ID_ from JBPM_VARIABLEINSTANCE);
commit;
alter table JBPM_BYTEARRAY disable all triggers;
insert /*append*/ into JBPM_BYTEARRAY select * from JBPM_BYTEARRAY2013 where ID_ in(select BYTEARRAYVALUE_ from JBPM_VARIABLEINSTANCE);
commit;
alter table JBPM_BYTEBLOCK disable all triggers;
insert /*append*/ into JBPM_BYTEBLOCK select * from JBPM_BYTEBLOCK2013 where PROCESSFILE_ in(select ID_ from JBPM_BYTEARRAY);
commit;
alter table JBPM_PROCESSINSTANCE enable all triggers;
alter table JBPM_MODULEINSTANCE enable all triggers;
alter table JBPM_TOKEN enable all triggers;
alter table JBPM_TOKENVARIABLEMAP enable all triggers;
alter table JBPM_SWIMLANEINSTANCE enable all triggers;
alter table JBPM_POOLEDACTOR enable all triggers;
alter table JBPM_TASKACTORPOOL enable all triggers;
alter table JBPM_TASKINSTANCE enable all triggers;
alter table JBPM_VARIABLEINSTANCE enable all triggers;
alter table JBPM_BYTEARRAY enable all triggers;
alter table JBPM_BYTEBLOCK enable all triggers;