Script to find the long running session in Database

Download the script to find the sessions which are running longer than 2 hours into the database.HERE

[oracle@node1 script]$ more long_running.sh
#!/bin/bash
clear scr
echo "************************************************"
echo "************* Long Running Session *************"
echo "************************************************"
sqlplus -S / as sysdba@$1<<EOF

set pages 5000 lines 150
col username for a10
col module for a30
col CLIENT_INFO for a30
col EVENT for a10
col SQL_TEXT for a20
col MACHINE for a10
col OSUSER for a15


select
s.last_call_et, w.event, sa.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 s.status = 'ACTIVE'
and s.logon_time > (select sysdate - 2 from dual) ;


EOF

OUTPUT.

[oracle@node1 script]$ sh long_running.sh

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

************* Long Running Session *************

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

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 me select s.last_call_e         35       3867 464D4738 gpazz41g6pycb               0          6 oracle          SYS

             ssage to c t, w.event, sa.sql_t

             lient      ext, s.sid, serial#,

                        sa.address, s.sql_id

                        ,parsing_user_id, ex

                        ecutions, s.osuser,

                        s.username, s.client

                        _info, s.machine, s.

                        process as spid, s.m

                        odule, to_char(sysda

                        te-(s.last_call_et/6

                        0/60/24),'MM/DD/YYYY

                         HH24:MI') waiting_t

                        ime, to_char(s.logon

                        _time,'MM/DD/YYYY HH

                        24:MI') logontime fr

                        om v$session s,v$ses

                        sion_wait w, v$sqlar

                        ea sa where s.sql_ad

                        dress = sa.address a

                        nd s.sql_hash_value

                        = sa.hash_value and

                        w.sid = s.sid and s.

                        status = 'ACTIVE' an

                        d s.logon_time > (se

                        lect sysdate - 2 fro

                        m dual)

                               node1.orac 6065                     sqlplus@node1.oracle.com (TNS  11/29/2013 00:58 11/29/2013 00:58

                               le.com                              V1-V3)