Skip to content
Oracle DBA Scripts & Database Utilities

Temporary Space and Lock Analysis (temp.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive analysis of database locks, identifying blocking and waiting sessions along with the objects they’re accessing. Essential for diagnosing lock contention issues and understanding the lock hierarchy in your database.

The Script

Section titled “The Script”
ttitle on
set linesize 250
set pagesize 60
column command format a15
column osuser heading 'OS|Username' format a7
column process heading 'OS|Process' format a7
column machine heading 'OS|Machine' format a10
column program heading 'OS|Program' format a18
column object heading 'Database|Object' format a25
column lock_type heading 'Lock|Type' format a4
column lock_description heading 'Lock Description' format a16
column mode_held heading 'Mode|Held' format a15
column mode_requested heading 'Mode|Requested' format a10
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7
column image heading 'Active Image' format a20
column sid format 99999
col waiting_session head 'WATR' format 9999
col holding_session head 'BLKR' format 9999
col name format a15
col description format a30


select /*+ ordered */
            l.kaddr
        ,   s.sid
        ,   s.username
        ,   lock_waiter.waiting_session
        ,   lock_blocker.holding_session
        ,   (select name from sys.user$ where user# = o.owner#) ||'.'||o.name   object
        ,   decode(command,0,'BACKGROUND',1,'Create Table',2,'INSERT',3,'SELECT',4,'CREATE CLUSTER',5,'ALTER CLUSTER',6,'UPDATE',7,'DELETE',8,'DROP',9,'CREATE INDEX',10,'DROP INDEX'
            ,11,'ALTER INDEX',12,'DROP TABLE',13,'CREATE SEQUENCE',14,'ALTER SEQUENCE',15,'ALTER TABLE',16,'DROP SEQUENCE',17,'GRANT',18,'REVOKE',19,'CREATE SYNONYM',20,'DROP SYNONYM'
            ,21,'CREATE VIEW',22,'DROP VIEW',23,'VALIDATE INDEX',24,'CREATE PROCEDURE',25,'ALTER PROCEDURE',26,'LOCK TABLE',27,'NO OPERATION',28,'RENAME',29,'COMMENT',30,'AUDIT'
            ,31,'NOAUDIT',32,'CREATE EXTERNAL DATABASE',33,'DROP EXTERNAL DATABASE',34,'CREATE DATABASE',35,'ALTER DATABASE',36,'CREATE ROLLBACK SEGMENT',37,'ALTER ROLLBACK SEGMENT',38,'DROP ROLLBACK SEGMENT',39,'CREATE TABLESPACE',40,'ALTER TABLESPACE'
            ,41,'DROP TABLESPACE',42,'ALTER SESSION',43,'ALTER USER',44,'COMMIT',45,'ROLLBACK',46,'SAVEPOINT',47,'PL/SQL EXECUTE',48,'SET TRANSACTION',49,'ALTER SYSTEM SWITCH LOG',50,'EXPLAIN'
            ,51,'CREATE USER',52,'CREATE ROLE',53,'DROP USER',54,'DROP ROLE',55,'SET ROLE',56,'CREATE SCHEMA',57,'CREATE CONTROL FILE',58,'ALTER TRACING',59,'CREATE TRIGGER',60,'ALTER TRIGGER'
            ,61,'DROP TRIGGER',62,'ANALYZE TABLE',63,'ANALYZE INDEX',64,'ANALYZE CLUSTER',65,'CREATE PROFILE',66,'DROP PROFILE',67,'ALTER PROFILE',68,'DROP PROCEDURE',69,'DROP PROCEDURE',70,'ALTER RESOURCE COST'
            ,71,'CREATE SNAPSHOT LOG',72,'ALTER SNAPSHOT LOG',73,'DROP SNAPSHOT LOG',74,'CREATE SNAPSHOT',75,'ALTER SNAPSHOT',76,'DROP SNAPSHOT',79,'ALTER ROLE'
            ,85,'TRUNCATE TABLE',86,'TRUNCATE CLUSTER',87,'-',88,'ALTER VIEW',89,'-',90,'-'
            ,91,'CREATE FUNCTION',92,'ALTER FUNCTION',93,'DROP FUNCTION',94,'CREATE PACKAGE',95,'ALTER PACKAGE',96,'DROP PACKAGE',97,'CREATE PACKAGE BODY',98,'ALTER PACKAGE BODY',99,'DROP PACKAGE BODY',command||'-UNKNOWN') COMMAND
        ,   lt.name
        ,   lt.description
        ,   decode   (      l.lmode,      0, 'None',           /* Mon Lock equivalent */      1, 'No Lock',           /* N */      2, 'Row-S (SS)',     /* L */      3, 'Row-X (SX)',     /* R */      4, 'Share',          /* S */      5, 'S/Row-X (SRX)',  /* C */      6, 'Exclusive',      /* X */      to_char(l.lmode)   ) mode_held,   decode   (      l.request,      0, 'None',           /* Mon Lock equivalent */      1, 'No Lock',           /* N */      2, 'Row-S (SS)',     /* L */      3, 'Row-X (SX)',     /* R */      4, 'Share',          /* S */      5, 'S/Row-X (SSX)',  /* C */      6, 'Exclusive',      /* X */      to_char(l.request)   ) mode_requested
        ,   s.osuser
        ,   s.machine
        ,   s.program
        ,   s.process
     from v$lock_type lt
     join v$lock l
       on lt.type = l.type
     join v$session s
       on s.sid = l.sid
left join sys.dba_waiters lock_blocker
       on lock_blocker.waiting_session = s.sid
left join sys.dba_waiters lock_waiter
       on lock_waiter.holding_session = s.sid
left join sys.obj$ o
       on o.obj# = l.id1
    where s.type != 'BACKGROUND'
/

Parameters

Section titled “Parameters”

The script prompts for:

  • None - this script requires no parameters

Usage

Section titled “Usage”
-- Basic usage
@temp.sql

Sample Output

Section titled “Sample Output”
KADDR            SID Oracle WATR BLKR Database                 COMMAND         NAME            DESCRIPTION                    Mode            Mode       OS      OS      OS Machine    OS Program         OS
                     Username                Object                                               Held            Requested   Username Process                                   Process
---------------- ----- ------- ---- ---- ------------------------- --------------- --------------- ------------------------------ --------------- ---------- ------- ------- ---------- ------------------
00000000600012B0   145 SCOTT      0    0 SCOTT.EMPLOYEES          SELECT          TM              DML                            Row-S (SS)      None       oracle  12345   db01       sqlplus@db01
00000000600012C8   146 JOHN     145    0 SCOTT.EMPLOYEES          UPDATE          TM              DML                            Row-X (SX)      None       oracle  12346   db01       sqlplus@db01
00000000600012D0   147 MARY       0  146 SCOTT.EMPLOYEES          UPDATE          TM              DML                            None            Row-X (SX) oracle  12347   db01       sqlplus@db01