21 August 2026

Deadlocks (last hour)

by Kellyn Gorman

15 0 Performance Oracle

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

Oracle automatically resolves deadlocks by rolling back one statement and writing ORA-00060 to the alert log. Because the database recovers on its own, deadlocks are easy to miss — yet a rising deadlock rate points to an application locking-order problem.

Required permissions:

GRANT SELECT ON V_$DIAG_ALERT_EXT TO redgate_monitor_user CONTAINER=ALL;
GRANT SELECT ON V_$CONTAINERS TO redgate_monitor_user CONTAINER=ALL;

These are covered by Redgate Monitor’s standard Oracle monitoring user — if it was set up as described in the product documentation, no additional grants are needed.

Metric definition

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 — test execution time first, and purge old alert-log history if slow.

Supported database engine

Oracle

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			-- Returns the number of deadlocks (ORA-00060) in the alert log
-- during the last 60 minutes, per container
SELECT c.con_id,
       NVL(d.cnt, 0) AS metric_value,
       d.detail AS alert_detail_text
  FROM v$containers c
  LEFT JOIN (
        SELECT con_id,
               COUNT(*) AS cnt,
               MAX(TO_CHAR(originating_timestamp, 'HH24:MI') || ' ' || SUBSTR(message_text, 1, 200)) AS detail
          FROM v$diag_alert_ext
         WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '60' MINUTE
           AND message_text LIKE '%ORA-00060%'
         GROUP BY con_id
       ) d
    ON d.con_id = c.con_id
 WHERE c.name <> 'PDB$SEED'
   AND c.open_mode <> 'MOUNTED'
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
				
						-- Returns the number of deadlocks (ORA-00060) in the alert log
-- during the last 60 minutes, per container
SELECT c.con_id,
       NVL(d.cnt, 0) AS metric_value,
       d.detail AS alert_detail_text
  FROM v$containers c
  LEFT JOIN (
        SELECT con_id,
               COUNT(*) AS cnt,
               MAX(TO_CHAR(originating_timestamp, 'HH24:MI') || ' ' || SUBSTR(message_text, 1, 200)) AS detail
          FROM v$diag_alert_ext
         WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '60' MINUTE
           AND message_text LIKE '%ORA-00060%'
         GROUP BY con_id
       ) d
    ON d.con_id = c.con_id
 WHERE c.name <> 'PDB$SEED'
   AND c.open_mode <> 'MOUNTED'

Instances to collect from

Select all monitored Oracle instances (test query duration first on instances with large alert logs)

Databases to collect from

Not applicable — Oracle custom metrics collect once per instance, and values are stored per container (PDB) via the con_id column

Collection frequency

600

seconds (collected every 10 minutes)

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Deadlocks detected

Description

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.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	0
Medium:
Low:

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

15 Rate

Click to rate this post!

[Total: 0 Average: 0]

Deadlocks (last hour)

Metric definition

Name

Description

Supported database engine

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics