12c SOA - How to check BPEL recovery Pool from SOAInfra DB

Sometimes EM Composite BPEL instances have failures and thus they are moved to BPEL recovery pool. We can check  them from SOAINFRA DB and delete the struck instances if not needed.

Query 1: based on date

select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'

Query2: based on a period

select count(*) from dlv_message where dlv_type = 1 and state = 0 and receive_date 

between TO_DATE('2020-01-01 00:00:01', 'YYYY-MM-DD HH24:Mi:SS') and TO_DATE('2020-05-31 23:40:59', 'YYYY-MM-DD HH24:Mi:SS')

To delete:

delete from dlv_message where dlv_type = 1 and state = 0 and receive_date like '%08-JUL-19%'