I am sharing my SQL scripts to clean History tables. Please feel free to comment and advise. I select for backup and delete only data related to completed process instances. Thank you,
--------- Estimate the number of historic rows to backup ----------------------
SELECT ‘ACT_HI_ATTACHMENT’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_COMMENT’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_IDENTITYLINK’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_DETAIL’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_VARINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_TASKINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_ACTINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
UNION
SELECT ‘ACT_HI_PROCINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;
------- SELECT FOR BACKUP HISTORIC TABLE DATA FOR ALL COMPLETED PROCESSES -----
SELECT *
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;
----------------------- DELETE HISTORIC DATA -----------------------------------
DELETE
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;
DELETE
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;