Custom Ignite Alert: Oracle Locking in Instance#LR14349
This custom Ignite alert will run against the monitored Oracle databases and retrieve the time a session has been waiting ("Seconds Blocked" column) on a blocking session. It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from v$session.
select 'SID ' || blocker.sid || ' running ' || blocker.program || ' from machine ' || blocker.machine || ' logged in as ' || blocker.username || ' has been blocking SID ' || blocked.sid || ' for ' || blocked.last_call_et || ' seconds.' "Message", blocked.last_call_et "Seconds Blocked" from v$session blocked inner join v$session blocker on blocker.sid = blocked.blocking_session order by blocked.blocking_session
Ignite alert definition