Show waiting sessions and the event they wait on

  [code language=”sql”] #!/bin/ksh sqlplus -s "/ as sysdba" <<EOF select  sw.sid  sid, p.spid   spid, s.username username, s.osuser   osuser, sw.event event, s.machine  machine, s.program  program, decode(sw.event,’db file sequential read’, sw.p3, ‘db file scattered read’, sw.p3, null) blocks from v\$session_wait sw, v\$session  s, v\$process p where s.paddr = p.addr and event not in (‘pipe get’,’client message’) and […]

Find Locked Sessions

[code language=”sql”] set lines 100 pages 999 col username format a20 col sess_id format a10 col object format a25 col mode_held format a10 select oracle_username || ‘ (‘ || s.osuser || ‘)’ username, s.sid || ‘,’ || s.serial# sess_id, owner || ‘.’ || object_name object, object_type, decode( l.block, 0, ‘Not Blocking’, 1, ‘Blocking’, 2, ‘Global’) […]