SQL QUERY TO FIND THE HISTORICAL LOCKS AND BLOCKING SESSIONS FOR SQL_ID
This query maps objects-sql-locks to provide a connection between these three entities
Use it to analyze any historical locks which cause enqueues on any objects
set lines 200 pages 1000
col EVENT for a20
col LOCK_TYPE for a10
col OBJECT_NAME for a15
col MODE_HELD for a10
col MODE_REQUESTED for a10
col BLOCKING_OTHERS for a15
SELECT dhss.sql_id,
dhss.event,
do.object_name,
dl.lock_type,
dl.mode_held,
dl.mode_requested,
dl.blocking_others
FROM dba_hist_active_sess_history dhss
INNER JOIN dba_locks dl ON ( dl.session_id = dhss.session_id )
INNER JOIN dba_objects do ON ( do.object_id = dhss.current_obj# )
ORDER BY dl.blocking_others ASC
FETCH NEXT 5 ROWS ONLY;