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
/
--> ------------------------------------------------------------------------
No comments:
Post a Comment