      /* Print out the lock wait-for graph in a tree structured fashion.
      *
      * This script prints the sessions in the system that are waiting for
      * locks, and the locks that they are waiting for. The printout is tree
      * structured. If a sessionid is printed immediately below and to the right
      * of another session, then it is waiting for that session. The session ids
      * printed at the left hand side of the page are the ones that everyone is
      * waiting for.
      *
      * For example, in the following printout session 9 is waiting for
      * session 8, 7 is waiting for 9, and 10 is waiting for 9.
      *
      * WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
      * ----------------- ---- ----------------- ----------------- -------- --------
      * 8 NONE None None 0 0
      * 9 TX Share (S) Exclusive (X) 65547 16
      * 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
      * 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
      *
      * The lock information to the right of the session id describes the lock
      * that the session is waiting for (not the lock it is holding).
      *
      * Note that this is a script and not a set of view definitions because
      * connect-by is used in the implementation and therefore a temporary table
      * is created and dropped since you cannot do a join in a connect-by.
      *
      * This script has two small disadvantages. One, a table is created when
      * this script is run. To create a table a number of locks must be
      * acquired. This might cause the session running the script to get caught
      * in the lock problem it is trying to diagnose. Two, if a session waits on
      * a lock held by more than one session (share lock) then the wait-for graph
      * is no longer a tree and the conenct-by will show the session (and any
      * sessions waiting on it) several times.
      */


      /* Select all sids waiting for a lock, the lock they are waiting on, and the
      * sid of the session that holds the lock.
      * UNION
      * The sids of all session holding locks that someone is waiting on that
      * are not themselves waiting for locks. These are included so that the roots
      * of the wait for graph (the sessions holding things up) will be displayed.
      */
      drop table lock_holders;

      create table LOCK_HOLDERS /* temporary table */
      (
      waiting_session number,
      holding_session number,
      lock_type varchar2(26),
      mode_held varchar2(14),
      mode_requested varchar2(14),
      lock_id1 varchar2(22),
      lock_id2 varchar2(22)
      );

      drop table dba_locks_temp;
      create table dba_locks_temp as select * from dba_locks;

      /* This is essentially a copy of the dba_waiters view but runs faster since
      * it caches the result of selecting from dba_locks.
      */
      insert into lock_holders
      select w.session_id,
      h.session_id,
      w.lock_type,
      h.mode_held,
      w.mode_requested,
      w.lock_id1,
      w.lock_id2
      from dba_locks_temp w, dba_locks_temp h
      where h.blocking_others = 'Blocking'
      and h.mode_held != 'None'
      and h.mode_held != 'Null'
      and w.mode_requested != 'None'
      and w.lock_type = h.lock_type
      and w.lock_id1 = h.lock_id1
      and w.lock_id2 = h.lock_id2;

      commit;

      drop table dba_locks_temp;

      insert into lock_holders
      select holding_session, null, 'None', null, null, null, null
      from lock_holders
      minus
      select waiting_session, null, 'None', null, null, null, null
      from lock_holders;
      commit;

      column waiting_session format a17;
      column lock_type format a17;
      column lock_id1 format a17;
      column lock_id2 format a17;

      /* Print out the result in a tree structured fashion */
      select lpad(' ',3*(level-1)) || waiting_session waiting_session,
      lock_type,
      mode_requested,
      mode_held,
      lock_id1,
      lock_id2
      from lock_holders
      connect by prior waiting_session = holding_session
      start with holding_session is null;

      drop table lock_holders;