INTRODUCTION
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%.
ALERT DEFINITION
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
from
(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
