Alfresco Activiti - clean non-existent historical process data (fix "My completed tasks" error)

alfresco_clean_hist_processes.sql

-- =============================================
-- Author: howkymike
-- Description: deletes nonexistent historical process data. It resolves the "My completed tasks" error after some 
--  process definitions has been deleted via Alfresco Workflow console.
-- Before executing this script stop Alfresco instace
-- =============================================

-- Create temp table which stores faulty IDs
SELECT PROC_INST_ID_ into tmp2 FROM ACT_HI_PROCINST 
left join act_re_procdef on act_re_procdef.id_ = act_hi_procinst.proc_def_id_ 
where act_re_procdef.id_ is null;

-- Delete all records connected to those processes
DELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_ATTACHMENT WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_COMMENT WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_DETAIL WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_IDENTITYLINK WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);

-- Drop tmp2 table
drop table tmp2;


vacuum full;