Script to find the waiting sessions

Download the script from HERE.

[oracle@node1 script]$ more waiting.sh
#!/bin/bash
clear scr
echo "**********************************************"
echo "*********** Waiting Session Info *************"
echo "**********************************************"

sqlplus -S / as sysdba@$1<<EOF


set pages 5000 lines 150
select
s.last_call_et, w.event, sql_text, s.sid, serial#, sa.address, s.sql_id,parsing_user_id, executions,
s.osuser, s.username, s.client_info, s.machine, s.process as spid, s.module, to_char(sysdate-(s.last_call_et/60/60/24),'MM/DD/YYYY HH24:MI') waiting_time, to
_char(s.logon_time,'MM/DD/YYYY HH24:MI') logontime
from v\$session s,v\$session_wait w, v\$sqlarea sa
where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and w.sid = s.sid
and   w.event not like 'null event' and
  w.event != 'rdbms ipc message' and
  w.event != 'pipe get' and
  w.event != 'queue messages' and
  w.event != 'jobq slave wait' and
  w.event != 'virtual circuit status' and
  w.event not like '%timer%'
  and w.event not like 'SQL*Net message from%'
  and s.status != 'KILLED'
--and s.audsid != 0
order by last_call_et;


EOF

OUTPUT.

[oracle@node1 script]$ sh waiting.sh node1

**********************************************

*********** Waiting Session Info *************

**********************************************

LAST_CALL_ET EVENT

------------ ----------------------------------------------------------------

SQL_TEXT

------------------------------------------------------------------------------------------------------------------------------------------------------

       SID    SERIAL# ADDRESS  SQL_ID        PARSING_USER_ID EXECUTIONS OSUSER                         USERNAME

---------- ---------- -------- ------------- --------------- ---------- ------------------------------ ------------------------------

CLIENT_INFO                                                      MACHINE

---------------------------------------------------------------- ----------------------------------------------------------------

SPID                     MODULE                                                           WAITING_TIME     LOGONTIME

------------------------ ---------------------------------------------------------------- ---------------- ----------------

           0 SQL*Net message to client

select s.last_call_et, w.event, sql_text, s.sid, serial#, sa.address, s.sql_id,parsing_user_id, executions, s.osuser, s.username, s.client_info, s.mac

hine, s.process as spid, s.module, to_char(sysdate-(s.last_call_et/60/60/24),'MM/DD/YYYY HH24:MI') waiting_time, to_char(s.logon_time,'MM/DD/YYYY HH24

:MI') logontime from v$session s,v$session_wait w, v$sqlarea sa where s.sql_address = sa.address and s.sql_hash_value = sa.hash_value and w.sid = s.si

d and   w.event not like 'null event' and   w.event != 'rdbms ipc message' and   w.event != 'pipe get' and   w.event != 'queue messages' and   w.event

 != 'jobq slave wait' and   w.event != 'virtual circuit status' and   w.event not like '%timer%'   and w.event not like 'SQL*Net message from%'   and

s.status != 'KILLED' --and s.audsid != 0 order by last_call_et

        69       2593 467A20C0 4pnkwjscbv2av               0          2 oracle                         SYS

                                                                 node1.oracle.com

6282                     sqlplus@node1.oracle.com (TNS V1-V3)                             11/29/2013 01:05 11/29/2013 01:05