oracle-wiki.net - Blocking Locks Simple

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

Blocking Locks Simple (Lightweight)

-- Show Blocked Sessions

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

COLUMN username FORMAT A15

COLUMN osuser FORMAT A8

COLUMN sid FORMAT 99999

COLUMN serial# FORMAT 99999

COLUMN process_id FORMAT A5

COLUMN wait_class FORMAT A12

COLUMN seconds_in_wait FORMAT 9999

COLUMN state FORMAT A17

COLUMN blocking_session 9999

COLUMN blocking_session_state a10

COLUMN module FORMAT a10

COLUMN logon_time FORMAT A20

SELECT

NVL(a.username, '(oracle)') AS username,

a.osuser,

a.inst_id,

a.sid,

a.serial#,

a.sql_id,

d.spid AS process_id,

a.wait_class,

a.seconds_in_wait,

a.state,

a.blocking_instance,

a.blocking_session,

a.blocking_session_status,

a.module,

TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM

gv$session a,

gv$process d

WHERE

a.paddr = d.addr

AND

a.inst_id = d.inst_id

AND

a.status = 'ACTIVE'

AND

a.blocking_session IS NOT NULL

ORDER BY 1,2

Notes on Blocking Locks Simple

This script displays active Oracle sessions that are currently being blocked by another database session.

It is particularly useful when investigating application hangs, transaction timeouts, lock contention or users reporting that a database operation appears to have stopped responding.

The script identifies

The blocked session
The RAC instance on which it is running (if applicable)
The session responsible for blocking it
The SQL being executed by the blocked session and information about its current wait state.

Because the script queries GV$SESSION and GV$PROCESS, it can be used on both single instance Oracle and Oracle RAC. BLOCKING_INSTANCE identifies the instance containing the blocking session and BLOCKING_SESSION identifies its SID. For single instance oracle BLOCKING_INSTANCE will be 1.

Main Colum Details and Explinations

INST_ID identifies the database instance containing the blocked session. (If not using RCA, this will be 1).

SID and SERIAL# identify the blocked database session. Both values should normally be used when performing session-level operations because a SID can subsequently be reused by another session.

SQL_ID identifies the SQL currently associated with the blocked session. This is the SQL that is waiting. Note this is not necessarily the SQL being executed by the blocking session.

PROCESS_ID is taken from GV$PROCESS.SPID and identifies the OS server process associated with the Oracle session. This can be useful when comparing database activity with OS diagnostics or tracing. Oracle defines SPID as the OS process identifier.

WAIT_CLASS provides the broad category of the session's current wait, such as Application, Concurrency, User I/O or Cluster.

STATE indicates whether the session is currently waiting or whether the displayed wait information relates to its previous wait. A value of WAITING means the session is currently waiting.

SECONDS_IN_WAIT shows how long the current wait has lasted when the session is waiting.

BLOCKING_INSTANCE identifies the RAC instance on which the blocking session resides.

BLOCKING_SESSION identifies the SID of the session currently blocking this session.

BLOCKING_SESSION_STATUS indicates the status of blocking session information. The most useful value is VALID, meaning that Oracle has identified the blocker and the BLOCKING_INSTANCE and BLOCKING_SESSION values can be used. Other possible states include NO HOLDER, NOT IN WAIT and UNKNOWN.

MODULE can identify the application component responsible for the database call when the application populates Oracle's module information.

LOGON_TIME shows when the blocked database session originally connected. It should not be confused with the time at which the blocking condition began.

Published on

Published 8th September 2026

Page updated

Google Sites

Report abuse