We have a system with activiti engine that does 200k workflows per day.
We need to delete old completed workflow and we try use activiti api but the java batch is too slow.
So we have to write this pl/sql procedure:
createor replace PROCEDURE MNT_CLEAN_FINISHED_WF AS BEGIN dbms_output.enable; DBMS_OUTPUT.put_line ('START MNT_CLEAN_FINISHED_WF...'); FOR RES IN(SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST WHERE END_TIME_ <sysdate-210and rownum<2) LOOP DBMS_OUTPUT.put_line ('DELETE WF INSTANCE:'|| RES.PROC_INST_ID_); DELETEFROM ACT_GE_BYTEARRAY WHERE ID_ in(SELECT BYTEARRAY_ID_ FROM ACT_HI_VARINST WHERE PROC_INST_ID_ =RES.PROC_INST_ID_); DELETEFROM ACT_HI_ACTINST WHERE PROC_INST_ID_=RES.PROC_INST_ID_; DELETEFROM ACT_HI_VARINST WHERE PROC_INST_ID_ =RES.PROC_INST_ID_; DELETEFROM ACT_HI_PROCINST WHERE PROC_INST_ID_=RES.PROC_INST_ID_; COMMIT; END LOOP; DBMS_OUTPUT.put_line ('END MNT_CLEAN_FINISHED_WF...'); END MNT_CLEAN_FINISHED_WF;
Could you tell us if is this approach correct? and if is there other tables that we must delete?
You can limit the amount of history records you want to capture by setting the historyLevel in your process engine configuration. Lower level history will not save variable updates and form data submissions. This may reduce the number of records you need to delete on a daily basis.