{"name":"Blocked Sessions","description":"Counts sessions currently waiting on a blocker, per container. The alert detail text carries the who into the alert itself (blocked SID and username, blocking SID, seconds in wait); to investigate further, query V$SESSION and follow BLOCKING_SESSION back to the holder, then join to V$SQL to see what each session is running.","tsql":"-- Returns the number of sessions currently blocked by another session, per container\r\nSELECT c.con_id,\r\n       COUNT(s.sid) AS metric_value,\r\n       LISTAGG(CASE WHEN s.sid IS NOT NULL\r\n                    THEN 'SID ' || s.sid || ' (' || s.username || ') blocked by SID '\r\n                         || s.blocking_session || ' for ' || s.seconds_in_wait || 's'\r\n               END, '; ' ON OVERFLOW TRUNCATE)\r\n           WITHIN GROUP (ORDER BY s.seconds_in_wait DESC) AS alert_detail_text\r\n  FROM v$containers c\r\n  LEFT JOIN v$session s\r\n    ON s.con_id = c.con_id AND s.blocking_session IS NOT NULL\r\n WHERE c.name <> 'PDB$SEED'\r\n   AND c.open_mode <> 'MOUNTED'\r\n GROUP BY c.con_id","instances":true,"databases":{"mode":1},"frequency":60,"frequency_comment":"seconds (collected every minute)","rateofchange":false,"metricenabled":true,"alertname":"Blocked sessions","alertdescription":"Sessions in this container are waiting on locks held by other sessions. Raise the threshold above 0 if your workload has expected brief blocking. The alert detail text shows which sessions are blocked and by whom.","aboveorbelow":"above","_thresholds_high":{"selected":true,"value":0},"collections":2,"alertenabled":true,"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"targetentitytype":2}