Display Session Waiting for Locks Using utllockt.sql script

The utllockt.sql script displays, in tree-structured fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for.

The following test is done with Oracle 10gR2 database:

In session 1:

scott@ORCL> update emp set job=lower(job) where empno=1000;

1 row updated.


In session 2:

scott@ORCL> update emp set ename=upper(ename) where empno = 1000;

In session 3:

scott@ORCL> delete from emp where empno=1000;




cd /cygdrive/c/oraclexe/app/oracle/product/10.2.0/server/RDBMS/ADMIN
sqlplus / as sysdba


sys@ORCL> @utllockt.sql

...

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD    LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- ------------ --------- -----------------
151               None
   135            Transaction       Exclusive      Exclusive      524333            13026
   147            Transaction       Exclusive      Exclusive      524333            13026



After commit in session 1, run utllockt.sql again:


WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135               None
   147            Transaction       Exclusive      Exclusive      131072            12916


After commit in session 2

scott@ORCL> delete from emp where empno=1000;

1 row deleted.

scott@ORCL> rollback;

Rollback complete.

scott@ORCL> select * from emp where empno=1000;

     EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
      1000 DENNISII