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:
create or replace PROCEDURE MNT_CLEAN_FINISHED_WF AS
DBMS_OUTPUT.put_line ('START MNT_CLEAN_FINISHED_WF...');
FOR RES IN (SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST WHERE END_TIME_ <sysdate-210 and rownum<2)
DBMS_OUTPUT.put_line ('DELETE WF INSTANCE:' || RES.PROC_INST_ID_);
DELETE FROM ACT_GE_BYTEARRAY WHERE ID_ in (SELECT BYTEARRAY_ID_ FROM ACT_HI_VARINST WHERE PROC_INST_ID_ =RES.PROC_INST_ID_);
DELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_=RES.PROC_INST_ID_;
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ =RES.PROC_INST_ID_;
DELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_=RES.PROC_INST_ID_;
DBMS_OUTPUT.put_line ('END MNT_CLEAN_FINISHED_WF...');
Could you tell us if is this approach correct? and if is there other tables that we must delete?