Description

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.

SQL Statement

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