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; 

好了，大功告成！