Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Percent Locks Used


This alert will review the number of locks currently being used and compare to the max_locks parameter. The thresholds defined below will send a warning message at 80-90%, and a critical above 90%.


To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

Alert Name: Percent Locks Used

Execution Interval: 10 Minutes

Notification Text: This database is close to using the maximum number of locks as defined by max_locks parameter

SQL Statement:

select a.cts/b.max_lock*100


(select count(*) cts from v$lock) a,

(select value max_lock from v$parameter where upper(name)='DML_LOCKS') b

Execute Against: Monitored Instance

Units: Percent of Locks Used

High Threshold: MIN 90, MAX empty

Medium Threshold: MIN 80, MAX 90

Labels (1)
Version history
Revision #:
1 of 1
Last update:
‎02-21-2014 04:27 PM
Updated by: