spool locking3.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "OWNER" on "TABLE NAME" on "INDEX NAME";
 
/* ************************************************ */
/*                                                  */
/* substitute the table_name variable with the new  */
/* name in CAPITAL LETTERS                          */
/*  - will show all the going on within the dbms    */
/*                                                  */
/*         @c:\oracle\sql\locking3.sql              */ 
/*                                                  */
/* ************************************************ */

/* ********************************************************* */
/* This query defines the transactions involved in a locking */
/*      situation:                                           */
/* ********************************************************* */
 
column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
 
select A.username  "Waiting User",
       A.osuser    "OS User",
       A.sid       "SID",
       B.username  "Holding User",
       B.osuser    "OS User",
       B.sid       "SID"
  from v$session   A,
       v$session   B,
       dba_waiters C
 where C.waiting_session = A.sid and
       C.holding_session = B.sid
 order by 
       B.waiting_session,
       B.holding_session;

set echo off;
spool off;
set long 80;
commit;










