Useful scripts to identify database sessions and locks


	Applies To

	Product(s):	Exor Network Manager
	Version(s):	N/A
	Environment:	N/A
	Area:	Other
	Subarea:
	Original Author:	Sarah Williams, Bentley Technical Support Group

Problem Description

Sometimes I encounter a 'lock' message when trying to update records and wish to check who is logged in and who is locking which objects so I can resolve the issue.

Reason

In Oracle databases, locks are used to maintain the integrity of the data. Depending on the lock type, users may either see the form 'hang' when they try to enter or update data, or may see an error message advising the record is locked.

Steps to Resolve

Option 1 The manual method!

Probably the quickest and easiest way if all users operate in the same location is to ask around if someone else is working on the same data and ask them to let you know as soon as they have committed their changes so you can then make your changes to the record.

Option 2 Scripts

The following scripts can be run by your database administrator or system administrator in SQL*Plus (for Managed Service users please contact TSG):

sessions.sql - this lists all sessions logged in to the database. Any listed as 'active' are currently running something. Any 'inactive' but locking a table (see next script) may have paused in the middle of updating / inserting and be doing something else outside of Highways.
coe_locks.sql - lists all locks on the database, the username that has the lock and the session ID which can be linked back to the previous script output.
active_sql.sql - shows the session information, username and SQL command being run by anyone who has an active session currently.

The scripts can be found here.

To run the scripts, save the zip file somewhere on the machine where SQL*Plus is installed, e.g. c:\temp and unzip it. Log into SQL*Plus as the Highways owner user (usually highways or atlas) and enter the command to run, e.g.:

SQL> start c:\temp\sessions.sql

(replace c:\temp with the location where you stored the files)

Option 3 TSG

Very occasionally the locks may be due to a software issue rather than a user action. If all the above have failed then log a forum post and TSG will assist.