topic Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables in Alfresco Archive

How to delete data from act_ge_bytearray and act_hi_actiinst tables

ujjwalchoudhari — Tue, 24 May 2016 07:09:50 GMT

My application is on production and data in the above 2 table is huge. I want to clean up data. Although my ACT_HI_DETAIL tables is empty.My concerns are :-1. Does any body got the way to clear ACT_GE_BYTEARRAY table? how to find the records from bytearray table to be deleted? what is the relation o

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

hari — Tue, 24 May 2016 08:53:19 GMT

Hi Ujjwal,

I believe deleting is not a good option to consider that too for production environment. Try to index the tables and see if it helps.
btw whats the amount of data in those tables ?

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

ujjwalchoudhari — Tue, 24 May 2016 09:01:47 GMT

Hi Thanks for the reply.

act_hi_varinst –> 9.085.976 records
ACT_GE_BYTEARRAY –> 2.095.225 rows
and estimated zipped(!) size of 7-8GB!

I think i am not using any history data in my application. I guess it can be accessed only through history service api's of activiti.
And i am not using any history service api.

If i delete all history data and all the byte array table data for those history records will it work? Or i will be in a bigger mess?

something like this below :-

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

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

jbarrez — Tue, 31 May 2016 10:09:44 GMT

If you delete history information, do realize you're throwing away all your 'audit data' about past process instances.

You can safely delete historical data from process instance that are not in the act_ru_execution table anymore.

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

maudrid — Wed, 01 Jun 2016 10:27:29 GMT

I've deleted records from this table in the past with activiti 6.
The side effect is that I had to just redeploy the processes, because the bpm data is also kept there.
In this case I was removing all run-time and history data but keeping the models, because we wanted to start with a clean database but keep the process definitions.

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

ujjwalchoudhari — Wed, 01 Jun 2016 12:05:49 GMT

Does the below query make sense?

SELECT p.ID_ AS PROCESS_ID
    FROM ACT_HI_PROCINST p
    INNER JOIN ACT_HI_TASKINST t on p.ID_ = t.PROC_INST_ID_
    WHERE p.END_TIME_< = rocess_end_timestamp
    AND t.delete_reason_ = 'completed';

Here I am selecting all the process instance id's whose task are completed and who have end time less then some time period in past.
By doing this i will delete alll completed task prcoess instance history data.

Will this query give a different and wrong result If I am not using ACT_RU_EXECUTION table.
Or should i select the Process instance id like this :-
SELECT p.ID_ AS PROCESS_ID
    FROM ACT_HI_PROCINST p
where p.ID_ not in ( Select PROC_INST_ID_ from ACT_RU_EXECUTION );

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

jbarrez — Fri, 03 Jun 2016 07:58:37 GMT

No, this query is ok. END_TIME_ will be null if the process instance is still in the ACT_RU_EXECUTION table

Re: How to delete data from act_ge_bytearray and act_hi_actiinst tables

ujjwalchoudhari — Fri, 03 Jun 2016 08:04:07 GMT

Thank you for your reply