21 August 2026

Blocked Sessions

by Kellyn Gorman

22 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.

A blocked session is one waiting on a lock held by another session. A few brief blocks are normal; sustained or growing blocking indicates a contention problem or a long-running transaction that is holding locks.

Required permissions:

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

These are covered by Red gate 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

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.

Supported database engine

Oracle

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			-- Returns the number of sessions currently blocked by another session, per container
SELECT c.con_id,
       COUNT(s.sid) AS metric_value,
       LISTAGG(CASE WHEN s.sid IS NOT NULL
                    THEN 'SID ' || s.sid || ' (' || s.username || ') blocked by SID '
                         || s.blocking_session || ' for ' || s.seconds_in_wait || 's'
               END, '; ' ON OVERFLOW TRUNCATE)
           WITHIN GROUP (ORDER BY s.seconds_in_wait DESC) AS alert_detail_text
  FROM v$containers c
  LEFT JOIN v$session s
    ON s.con_id = c.con_id AND s.blocking_session IS NOT NULL
 WHERE c.name <> 'PDB$SEED'
   AND c.open_mode <> 'MOUNTED'
 GROUP BY c.con_id
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
				
						-- Returns the number of sessions currently blocked by another session, per container
SELECT c.con_id,
       COUNT(s.sid) AS metric_value,
       LISTAGG(CASE WHEN s.sid IS NOT NULL
                    THEN 'SID ' || s.sid || ' (' || s.username || ') blocked by SID '
                         || s.blocking_session || ' for ' || s.seconds_in_wait || 's'
               END, '; ' ON OVERFLOW TRUNCATE)
           WITHIN GROUP (ORDER BY s.seconds_in_wait DESC) AS alert_detail_text
  FROM v$containers c
  LEFT JOIN v$session s
    ON s.con_id = c.con_id AND s.blocking_session IS NOT NULL
 WHERE c.name <> 'PDB$SEED'
   AND c.open_mode <> 'MOUNTED'
 GROUP BY c.con_id

Instances to collect from

Select all monitored Oracle instances

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

seconds (collected every minute)

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

Blocked sessions

Description

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.

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

2 collections

Alert is

Enabled

22 Rate

Click to rate this post!

[Total: 0 Average: 0]

Blocked Sessions

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