Spotlight on Oracle 10.10 - Getting Started Guide

The Blocking Locks page shows a lock tree of sessions that are blocked (waiting for lock requests), and the processes that are blocking them. At the root of the lock tree are the processes that hold locks, but are not waiting for locks. Processes that are waiting for locks are shown underneath the blocking processes.

To open the Blocking Locks page

Select the Spotlight on Oracle RAC connection in the Spotlight Browser.
Click Activity | Blocking Locks.

Actions on the Blocking Locks Grid

Action

Description

Select (Highlight) a session

Open the Top Sessions | Session Details Page for the selected session.

Kill a session

Right-click the session and select Kill Session | …

Option	Description
Kill Session	End the session after rolling back transactions, releasing locks and blocks, recovering system resources, and waiting for calls to remote databases.
Kill Session Immediate	End the session immediately, while performing the "kill session" functions in the background.
Disconnect Session Immediate	Disconnect the session and recover the entire session state immediately, without waiting for ongoing transactions to complete.
Disconnect Session Post Transaction	Wait for ongoing transactions to complete before disconnecting the session.
Disconnect Session Post Transaction Immediate	If there are on-going transactions, wait for the transactions to complete before disconnecting the session. If there are NO on-going transactions, disconnect the session immediately.

A message appears that contains the session identifier and serial number of the session to be killed, and the number of rollback segment blocks used. Click Yes to kill the session.

Note: Not all users have permission to kill sessions. This permission is granted by the Database Administrator. It is set when the user is created using the Oracle User Wizard.

Trace a session

Collect performance statistics and diagnostic data for the session.

To Trace a session

Right-click the session and select Trace a Session | ...

Option	Description
Basic (level 1)	Retrieve the basic details of the SQL statements that are currently being processed in the session.
Bind variable (level 4)	Retrieve basic details as above, plus bind variable values for the session where bind variables are used.
Wait events (level 8)	Retrieve basic details as above, plus wait details for the session where processing waits for an event to complete before continuing.
Waits and binds (level 12)	Retrieve basic details as above, plus bind variable and wait details for the relevant session.

The details of the selected session are now shown in a "highlight" color (red by default). For more information see your Database Administrator.

To end a trace session

Right-click the session and select End Trace Session

The details of the session are now displayed in the "normal" color (black by default). For more information see your Database Administrator.

View the details of a lock.

To view details

Right click the session and select Show Lock

Tip: For information about minimizing locks, see Reduce Lock Contention.

Grid: Blocking Locks

Column	Description
SID	Session Identifier.
Serial #	Session Serial Number.
Instance	The instance in the Oracle RAC cluster where the session is running.
User name	Oracle account name of the session.
PID	Process Identifier.
Lock type	Type of lock requested by user.
Mode wanted	Mode of the lock requested
Object wanted	The object on which the lock is requested.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Spotlight on Oracle 10.10 - Getting Started Guide

Blocking Locks Page