Query to Find The Inactive Users

SET ECHO off

--> NAME:   TFSINSES.SQL

--> USAGE:"@path/tfsinses.sql"

--> ------------------------------------------------------------------------

--> REQUIREMENTS:

-->    SELECT on V$SESSION, V$PROCESS, V$SESSION_WAIT

--> ------------------------------------------------------------------------

--> PURPOSE:

-->    This script lists inactive users in the database.  The wait

-->    sequence can be monitored to check whether this really is an

-->    inactive user or not.  The process id s can assist you to

-->    remove the process

--> ------------------------------------------------------------------------

--> EXAMPLE:

-->                                         Shadow     Parent      Wait

-->    ORACLE/OS User   Term    SID SERIAL# Process ID Process ID  Sequence

-->    ---------------- ------ ---- ------- ---------- ---------- ---------

-->    SYSTEM usupport  ttype     6      21 26351      26350       28

-->

--> ------------------------------------------------------------------------

--> DISCLAIMER:

-->    This script is provided for educational purposes only. It is NOT

-->    supported by Oracle World Wide Technical Support.

-->    The script has been tested and appears to work as intended.

-->    You should always run new scripts on a test instance initially.

--> ------------------------------------------------------------------------

--> Main text of script follows:

set heading on feedback on pages 66

column userinfo heading "ORACLE/OS User" format a19

column terminal heading "Term" format a6

column process heading "Parent|Process ID" format a10

column spid heading "Shadow|Process ID" format a10

column seq# heading "Wait|Sequence" format 99999990

select

                        s.username||' '||s.osuser userinfo,

                        s.terminal,

                        s.sid,

                        s.serial#,

                        p.spid,

                        s.process ,

                        w.seq#

from

                        v$session s,

                        v$process p,

                        v$session_wait w

where 1=1

                        and p.addr = s.paddr

                        and s.sid = w.sid

                        and w.event = 'SQL*Net message from client'

                        and s.status = 'INACTIVE'

order by

                        s.osuser,

                        s.terminal

/

--> ------------------------------------------------------------------------