Description

This 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

Alert Definition