This script provides information about all objects that are currently in a locked status in any locked mode. Back to SQL scripts

SET FEEDBACK OFF ECHO OFF VERIFY OFF
SET LINES 100 PAGES 50

COLUMN object_name HEAD "Object" FORMAT A30
COLUMN object_type HEAD "Type"
COLUMN oracle_username HEAD "Oracle User" FORMAT A20
COLUMN os_user_name HEAD "OS User" FORMAT A20
COLUMN locked_mode HEAD "Lock Mode" FORMAT A13
COLUMN command_desc HEAD "Command" FORMAT A30
COLUMN Machine HEAD "Machine" FORMAT A15
COLUMN program HEAD "Program" FORMAT A20

SELECT o.object_name,
o.object_type,
l.oracle_username,
l.os_user_name,
decode(l.locked_mode,0,'None',1,'NULL',2,'Row-S(SS)',
3,'Row-X(SX)',4,'Share', 5,'S/Row-X(SSX)',
6, 'Exclusive (X)') locked_mode,
s.machine,s.program
FROM all_objects o, v$locked_object l, v$session s
WHERE l.session_id = s.sid
AND l.object_id = o.object_id
/

SET FEEDBACK ON

To run the code, either cut and paste it onto your favourite editor and save the file to say locks.sql. Or you can download the script file here .

At the SQL*PLUS prompt, execute the script using the syntax
SQL>@locks
On execution, the script displays information about the currently locked objects in the database.

As an example, consider the following steps:

  1. First we lock the emp table in exclusive mode as follows:-
    SQL> LOCK TABLE emp IN EXCLUSIVE MODE;

  2. We then try to lock the dept table by deleting a row from it
    SQL> DELETE dept WHERE deptno = 20;

  3. We then execute our locks.sql script to show information w.r.t. these two locks.
    SQL> @locks

The result of running the locks.sql script is as follows:-

Object Type Oracle User OS User
------------------------------ ------------------ -------------------- --------------------
Lock Mode Machine Program
------------- --------------- --------------------
EMP TABLE SCOTT CPATERSO
Exclusive (X) PC00097 SQLPLUSW.EXE
DEPT TABLE SCOTT CPATERSO
Row-X(SX) PC00097 SQLPLUSW.EXE
Back to SQL scripts
Hosted by www.Geocities.ws

1