3.40 TopLockUsers

Use this Knowledge Script to monitor the current number of user‑held locks on an Oracle database. If the number of locks exceeds the threshold, the job raises an event.

You can specify the number of user sessions holding the most locks to display in the Event Properties dialog box, or enter 0 to display all sessions. Information in the Event Properties dialog box includes the number of locks held by each session, username, session ID, and program name.

The account you use to run this script must have SELECT permissions for the following tables:

DBA_USERS

V_$LOCK

V_$SESSION

V_$VERSION

3.40.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.

3.40.2 Default Schedule

The default interval for this script is Every 30 minutes.

3.40.3 Setting Parameter Values

Set the following parameters as needed:

Description	How to Set It
Oracle Username	Enter the username that this script needs to access the target databases. If you run this script on more than one database, configure each database with the same username. To use SYSDBA authentication, leave this parameter blank. The default is blank.
Collect data for locks held by top N user sessions?	Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current number of user-held locks by the user sessions with the highest number of locks. The default is unselected.
Number of user sessions to display	Specify the number of user sessions with the most locks that you want displayed in the Event Properties dialog box. Enter 0 to display all user sessions. The default is 15.
Raise event if threshold is exceeded?	Select Yes to raise an event if the number of user-held locks on the server exceeds the threshold you set. The default is Yes.
Threshold - Maximum number of locks held by a user session	Enter a threshold for the maximum number of user‑held locks on an Oracle RDBMS. The default is 35.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10.
Event severity for internal failure	Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator).