My Oracle Support Banner

EM 12c: How Create Metric Extension For Blocking Session Count in Enterprise Manager CLoud Control (Doc ID 2104115.1)

Last updated on FEBRUARY 20, 2019

Applies to:

Enterprise Manager for Oracle Database - Version and later
Information in this document applies to any platform.


Enterprise Manager (EM) can be affected by this bug:

<BUG 19793192> - Some of the Blocking Session Count Alerts are not cleared

 BUG is fixed on Enterprise Manager (EM)

On Enterprise Manager (EM) apply Agent Bundle Patch (EM-AGENT-BP) <Patch 20822942> or later.

As a workaround, a Metric Extension (ME) can be created using the following sql statement :

SELECT a.blocking_sid, SUM(num_blocked) num_blocked
, id2
, MAX(DECODE(block, 1, sid, 0)) blocking_sid
, SUM(DECODE(request, 0, 0, 1)) num_blocked
FROM v$lock
WHERE block = 1
OR request > 0
GROUP BY id1, id2) a
, v$session b
WHERE a.blocking_sid=b.sid
and b.username is not NULL
GROUP BY blocking_sid





To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.