elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Looking for locks

A nice little script to show what objects are being locked. Usefully, it also brings out the process ID which you can use to kill the process using your operating system.


 

CONNECT SYSTEM/YOUR_PASSWORD_HERE

colsess format a12

colosuser format a20

col machine format a20

colobject_name format a20

colpid format a7

spool locks.txt

SELECT

        DECODE(vl.request,0,'Holder: ','Waiter: ') || vs.sid || ‘,’ || vs.serial#  sess,

        vs.osuser user,

        vs.machine machine,

        substr(vp.spid,1,9) pid,

        o.object_name obj

FROM

        V$LOCK vl,

        v$sessionvs,

        v$processvp,

        dba_objects

WHERE

        (vl.id1, vl.id2, vl.type) IN ( SELECT id1, id2, type FROM V$LOCK WHERE request>0)

and

        o.object_id  = vl.id1

and

        vp.addr=vs.paddr

and

        vs.sid=vl.sid

ORDER BY

        vl.id1, vl.request

/

spool off

exit

A typical output is shown below

 

SESS                        USER           MACHINE                PID                      OBJ

------------          -------      --------                -------                ---

Holder: 204,171     user1           PC1                       df21809                CAPS

Waiter: 186,110      user2          CHAS                     d8293                   CAPS

 

In order to kill the offending process (i.e PID df21809 in this case) using sqlplus you would simply – from a session with appropriate permissions – type in the following:

 

SQL> ALTER SYSTEM KILL SESSION '204,171’ immediate;