Reg. Actviti History tables that need to be purged

[hepcibha]

Hi,

We are using activiti engine in our clustered prod env and a number of processes get run on day to day basis. We have our history logging set to 'full', so that we have enough data to investigate any workflow related issues, we are trying to bump this down to 'audit' due to the  magnitude of data being logged but that will need some application changes to log what might not get logged in audit mode; we are working on that.

In order to have the history table sizes manageable, we added an oracle job to purge data from the history and related data from act_he_bytearray tables. Below is an extract of that job, please let me know if there are any other tables that we need to purge?

———————–
CURSOR ACT_Completed_ProcessIds
  IS
    SELECT ID_ AS PROCESS_ID
    FROM ACT_HI_PROCINST
    WHERE END_TIME_<= cast(sysdate - l_retention_period as timestamp ); —
BEGIN
  OPEN ACT_Completed_ProcessIds;

  LOOP
   CNT := CNT+1;
    FETCH ACT_Completed_ProcessIds into processId;
   EXIT WHEN ACT_Completed_ProcessIds%NOTFOUND;

    DELETE
    FROM ACT_HI_ATTACHMENT
    WHERE proc_inst_id_=processId;
   
    DELETE
    FROM ACT_HI_COMMENT
    WHERE proc_inst_id_=processId;

    DELETE from act_ge_bytearray  where id_ in
    (select bytearray_id_ FROM ACT_HI_DETAIL
      WHERE proc_inst_id_=processId
      AND var_type_ = 'serializable');

    DELETE from act_ge_bytearray where id_ in
    (select bytearray_id_ FROM ACT_HI_VARINST
      WHERE proc_inst_id_=processId
      AND var_type_ = 'serializable');

    DELETE
    FROM ACT_HI_DETAIL
    WHERE proc_inst_id_=processId;
   
    DELETE
    FROM ACT_HI_TASKINST
    WHERE proc_inst_id_=processId;
   
    DELETE
    FROM ACT_HI_VARINST
    WHERE proc_inst_id_=processId;
   
   DELETE
    FROM ACT_HI_IDENTITYLINK
    WHERE proc_inst_id_=processId;
   
    DELETE
    FROM ACT_HI_ACTINST
    WHERE proc_inst_id_=processId;
   
    DELETE
    FROM ACT_HI_PROCINST
    WHERE proc_inst_id_=processId;
   
   IF (CNT >=100) THEN
        COMMIT;
        CNT:=0;
    END IF;   
 
  END LOOP;
  COMMIT;
  close ACT_Completed_ProcessIds;
————-

Thanks
Hepci

[jbarrez]

Basically anything with ACT_HI_ is a history table. You can delete all you want. But obviously, do store it somewhere so it's not lost forever (in a file or something).