topic Reg. Actviti History tables that need to be purged in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/reg-actviti-history-tables-that-need-to-be-purged/m-p/183368#M136498 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><BR /><SPAN>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&nbsp; 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. </SPAN><BR /><BR /><SPAN>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?</SPAN><BR /><BR /><SPAN>———————–</SPAN><BR /><SPAN>CURSOR ACT_Completed_ProcessIds</SPAN><BR /><SPAN>&nbsp; IS</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; SELECT ID_ AS PROCESS_ID</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_PROCINST</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE END_TIME_&lt;= cast(sysdate - l_retention_period as timestamp ); —</SPAN><BR /><SPAN>BEGIN</SPAN><BR /><SPAN>&nbsp; OPEN ACT_Completed_ProcessIds;</SPAN><BR /><BR /><SPAN>&nbsp; LOOP</SPAN><BR /><SPAN>&nbsp;&nbsp; CNT := CNT+1;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FETCH ACT_Completed_ProcessIds into processId;</SPAN><BR /><SPAN>&nbsp;&nbsp; EXIT WHEN ACT_Completed_ProcessIds%NOTFOUND;</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_ATTACHMENT </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_COMMENT </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE from act_ge_bytearray&nbsp; where id_ in</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; (select bytearray_id_ FROM ACT_HI_DETAIL </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND var_type_ = 'serializable');</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE from act_ge_bytearray where id_ in</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; (select bytearray_id_ FROM ACT_HI_VARINST </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND var_type_ = 'serializable');</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_DETAIL</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_TASKINST </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_VARINST </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_IDENTITYLINK </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_ACTINST </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; DELETE</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; FROM ACT_HI_PROCINST </SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; WHERE proc_inst_id_=processId;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp;&nbsp; IF (CNT &gt;=100) THEN</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COMMIT;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CNT:=0;</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; END IF;&nbsp;&nbsp;&nbsp; </SPAN><BR /><SPAN>&nbsp; </SPAN><BR /><SPAN>&nbsp; END LOOP;</SPAN><BR /><SPAN>&nbsp; COMMIT;</SPAN><BR /><SPAN>&nbsp; close ACT_Completed_ProcessIds;</SPAN><BR /><SPAN>————-</SPAN><BR /><BR /><SPAN>Thanks</SPAN><BR /><SPAN>Hepci</SPAN></BODY></HTML> Thu, 14 May 2015 15:29:48 GMT hepcibha 2015-05-14T15:29:48Z Reg. Actviti History tables that need to be purged https://connect.hyland.com/t5/alfresco-archive/reg-actviti-history-tables-that-need-to-be-purged/m-p/183368#M136498 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&nbsp; magnitude of data Thu, 14 May 2015 15:29:48 GMT https://connect.hyland.com/t5/alfresco-archive/reg-actviti-history-tables-that-need-to-be-purged/m-p/183368#M136498 hepcibha 2015-05-14T15:29:48Z Re: Reg. Actviti History tables that need to be purged https://connect.hyland.com/t5/alfresco-archive/reg-actviti-history-tables-that-need-to-be-purged/m-p/183369#M136499 <HTML><HEAD></HEAD><BODY><SPAN>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).</SPAN></BODY></HTML> Mon, 18 May 2015 15:36:01 GMT https://connect.hyland.com/t5/alfresco-archive/reg-actviti-history-tables-that-need-to-be-purged/m-p/183369#M136499 jbarrez 2015-05-18T15:36:01Z