{"name":"Deadlocks (last hour)","description":"Counts deadlocks (ORA-00060) recorded in the alert log within the last 60 minutes, per container. Keep the collection interval shorter than the look-back window (here 10 minutes against a 60-minute window) so no deadlock is missed between collections. Querying V$DIAG_ALERT_EXT reads the alert log and can be heavy on instances with deep ADR history \u2014 test execution time first, and purge old alert-log history if slow.","tsql":"-- Returns the number of deadlocks (ORA-00060) in the alert log\r\n-- during the last 60 minutes, per container\r\nSELECT c.con_id,\r\n       NVL(d.cnt, 0) AS metric_value,\r\n       d.detail AS alert_detail_text\r\n  FROM v$containers c\r\n  LEFT JOIN (\r\n        SELECT con_id,\r\n               COUNT(*) AS cnt,\r\n               MAX(TO_CHAR(originating_timestamp, 'HH24:MI') || ' ' || SUBSTR(message_text, 1, 200)) AS detail\r\n          FROM v$diag_alert_ext\r\n         WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '60' MINUTE\r\n           AND message_text LIKE '%ORA-00060%'\r\n         GROUP BY con_id\r\n       ) d\r\n    ON d.con_id = c.con_id\r\n WHERE c.name <> 'PDB$SEED'\r\n   AND c.open_mode <> 'MOUNTED'","instances":true,"databases":{"mode":1},"frequency":600,"frequency_comment":"seconds (collected every 10 minutes)","rateofchange":false,"metricenabled":true,"alertname":"Deadlocks detected","alertdescription":"One or more deadlocks (ORA-00060) were recorded in the alert log for this container within the last hour. The alert detail text shows the most recent occurrence; full details and the trace file reference are in the alert log.","aboveorbelow":"above","_thresholds_high":{"selected":true,"value":0},"collections":1,"alertenabled":true,"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"targetentitytype":2}