Delete completed workflows?

[alecima]

Hi,

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 
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-210 and rownum<2)
LOOP
  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_;
  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?

Regards

Alessio

[gdharley]

This query looks correct.

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.

Greg

‌

[alecima]

Thank you.

We can't set a lower level of historyLevel because we need audit level for troubleshooting purpouse.