CREATE OR REPLACE VIEW spid_and_pid (
   pid,
   sid,
   ser#,
   box,
   username,
   server,
   inst_id,
   os_user,
   program )
AS
SELECT   SUBSTR (a.spid, 1, 9) pid, SUBSTR (b.SID, 1, 5) SID,
         SUBSTR (b.serial#, 1, 5) ser#, SUBSTR (b.machine, 1, 6) box,
         SUBSTR (b.username, 1, 10) username,
         b.server, b.inst_id,
         SUBSTR (b.osuser, 1, 8) os_user,
         SUBSTR (b.program, 1, 30) program
    FROM gv$session b, gv$process a
   WHERE b.paddr = a.addr AND TYPE = 'USER'
ORDER BY spid
/

CREATE OR REPLACE VIEW lock_holder (
   sess,
   id1,
   id2,
   lmode,
   request,
   type )
AS
SELECT DECODE(request,0,'Holder: ','Waiter: ') || 
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request
/

CREATE OR REPLACE VIEW lock_object (
   username,
   pid,
   spid,
   ora,
   vlock,
   type,
   lmode,
   wait )
AS
SELECT   p.username, p.pid, p.spid, s.username ora,
         DECODE (l2.TYPE,
                 'TX', 'TRANSACTION ROW-LEVEL',
                 'TS', 'TEMPORARY SEGMENT ',
                 'TD', 'TABLE LOCK',
                 'TM', 'ROW LOCK',
                 l2.TYPE
                ) vlock,
         DECODE (l2.TYPE,
                 'TX', 'DML LOCK',
                 'TS', 'TEMPORARY SEGMENT',
                 'TD', DECODE (l2.lmode + l2.request,
                               4, 'PARSE ' || u.NAME || '.' || o.NAME,
                               6, 'DDL',
                               l2.lmode + l2.request
                              ),
                 'TM', 'DML ' || u.NAME || '.' || o.NAME,
                 l2.TYPE
                ) TYPE,
         DECODE (l2.lmode + l2.request,
                 2, 'RS',
                 3, 'RX',
                 4, 'S',
                 5, 'SRX',
                 6, 'X',
                 l2.lmode + l2.request
                ) lmode,
         DECODE (l2.request, 0, NULL, 'WAIT') WAIT
    FROM v$process p,
         v$_lock l1,
         v$lock l2,
         v$resource r,
         SYS.obj$ o,
         SYS.user$ u,
         v$session s
   WHERE s.paddr = p.addr
     AND s.saddr = l1.saddr
     AND l1.raddr = r.addr
     AND l2.addr = l1.laddr
     AND l2.TYPE <> 'MR'
     AND l2.TYPE <> 'RT'
     AND r.id1 = o.obj#(+)
     AND o.owner# = u.user#(+)
     AND p.username LIKE NVL ('&&us_', '%')
ORDER BY 1, 2, 3, 4, 5
/