Be aware of this: Finding User sessions = idle > 30 min??

Hi all,

Be aware of the following caveat with LAST_CALL_ET:

A currently-executing SQL will NOT reset LAST_CALL_ET. I.e. if a session is 'ACTIVE' but the LAST_CALL_ET shows a large value (say your limit is 30 min) you _cannot_ kill this session as it is still executing that SQL, albeit long-running. The other possibility is that it is a standard Oracle background process (see below):

SQL> r
  1 select sid, program, last_call_et
  2 from v$session
  3* where status = 'ACTIVE' and last_call_et > 30

       SID PROGRAM                                          LAST_CALL_ET
---------- ------------------------------------------------ ------------
         1 oracle_at_usscora300 (PMON)                              1625534
         2 oracle_at_usscora300 (DBW0)                              1625534
         3 oracle_at_usscora300 (DBW1)                              1625534
         4 oracle_at_usscora300 (DBW2)                              1625534
         5 oracle_at_usscora300 (DBW3)                              1625534
         6 oracle_at_usscora300 (LGWR)                              1625534
         7 oracle_at_usscora300 (CKPT)                              1625534
         8 oracle_at_usscora300 (SMON)                              1625534
         9 oracle_at_usscora300 (RECO)                              1625534
        15 oracle_at_usscora300 (ARC0)                              1625534
       377 oracle_at_usscora300 (ARC1)                              1034585

11 rows selected.

In other words, looking at a combination of status='ACTIVE' and large last_call_et may _sometimes_ show up long running SQL.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

So WHO is the Reason for the Season?! Write me for details!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Ron Rogers [mailto:RROGERS_at_galottery.org]
> Sent: Thursday, December 12, 2002 4:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Finding User sessions = idle > 30 min??
>
>
> Denham,
> Thanks for the script. I modified it so the column OSUSER
> was selected
> also to show the network id that they logged in with.
> I use this script to see who is doing what on the system and who logs
> in with what id. I will modify it to add the last_call_et
> calculations.
> ++++++
> set linesize 90
> set pagesize 60
> COLUMN SU FORMAT A8 HEADING 'ORACLE|USER ID' JUSTIFY LEFT
> COLUMN OSU FORMAT A8 HEADING 'SYSTEM|USER ID' JUSTIFY LEFT
> COLUMN STAT FORMAT A8 HEADING 'SESSION|STATUS' JUSTIFY LEFT
> COLUMN SSID FORMAT 999999 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
> COLUMN SSER FORMAT 999999 HEADING 'ORACLE|SERIAL|NO' JUSTIFY RIGHT
> COLUMN SPID FORMAT A9 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
> COLUMN TXT FORMAT A25 HEADING 'CURRENT STATEMENT' JUSTIFY CENTER WORD
> COLUMN LOGTIME FORMAT A10 HEADING 'LOGIN|TIME' JUSTIFY RIGHT
>
> SELECT
> S.USERNAME SU,
> S.OSUSER OSU,
> to_char(S.LOGON_TIME,'MM-DD-YYYY HH:MI:SS') LOGTIME,
> S.STATUS STAT,
> S.SID SSID,
> S.SERIAL# SSER,
> LPAD(P.SPID,9) SPID,
> SUBSTR(SA.SQL_TEXT,1,540) TXT
> FROM V$PROCESS P,
> V$SESSION S,
> V$SQLAREA SA
> WHERE P.ADDR=S.PADDR
> AND S.USERNAME IS NOT NULL
> AND S.SQL_ADDRESS=SA.ADDRESS (+)
> AND S.SQL_HASH_VALUE=SA.HASH_VALUE (+)
> ORDER BY 1,3,6;
> ++++
> thanks again,
> Ron
>
> >>> EvaD_at_TFMC.co.za 12/11/02 04:39AM >>>
> Thanks to all who have contributed.
> Here is a solution based on Raj's email.
> Pls let me know if I am barking up the wrong tree..
>
> SELECT SID, SERIAL#, USERNAME, (LAST_CALL_ET / 60), LAST_CALL_ET,
> LOGON_TIME
> FROM v$session
> WHERE username IS NOT NULL
> AND (LAST_CALL_ET / 60) > 30
> AND STATUS = 'INACTIVE';
>
> Thanks
> Denham
>
>
>
> -----Original Message-----
> Sent: Tuesday, December 10, 2002 4:55 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Denham,
>
> How about using status='INACTIVE' and last_call_et > 30 minutes?
>
> Regards
> Raj
>
>
>
>
>
>
> Denham Eva
>
> <EvaD_at_TFMC.co To: Multiple
> recipients of
> list
> ORACLE-L <ORACLE-L_at_fatcity.com>
> .za> cc:
>
> Sent by: Subject: Finding User
> sessions
> = idle > 30 min??
> root_at_fatcity.
>
> com
>
>
>
>
>
> December 10,
>
> 2002 08:18 AM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Hello All,
>
>
> Please help - I am trying to find/create a script that will return all
> user
> sessions whose Idle time is greater than 30 minutes.
>
>
> ie
> SELECT SID, SERIAL#
> FROM V$SESSION
> " WHERE IDLE_TIME > 30 min;"
>
>
> My forays into the Documentation and searches have not been very
> successful.
> I don't really want to do this via the roles IDLE_TIME setting, I very
> much
> would like to be able to query directly to the database.
>
>
> Based on the information I would then make the decision to kill the
> user
> process etc.
> Just in case you might be interested it is Oracle 817 DB on Windows
> 2k.
>
>
> Best Regards
> Denham Eva
> Oracle DBA
> "UNIX is basically a simple operating system, but you have to be a
> genius
> to understand the simplicity."
> Dennis Ritchie.
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> ______________________________________________________________
> _______________
> DISCLAIMER
> This message is for the named person's use only. It may contain
> confidential,
> proprietary or legally privileged information. No confidentiality
> or privilege is waived or lost by any mistransmission. If you receive
> this message in error, please immediately delete it and all copies
> of it from your system, destroy any hard copies of it and notify the
> sender. You must not, directly or indirectly, use, disclose,
> distribute, print, or copy any part of this message if you are not
> the intended recipient. TFMC, its holding company, and any of its
> subsidiaries each reserve the right to monitor and manage all e-mail
> communications through its networks.
>
> Any views expressed in this message are those of the
> individual sender,
>
> except where the message states otherwise and the sender is authorized
>
> to state them to be the views of any such entity.
> ______________________________________________________________
> ______________
>
> ______________________________________________________________
> _______________________
> This e-mail message has been scanned for Viruses and Content and
> cleared
> by MailMarshal
>
> For more information please visit www.marshalsoftware.com
> ______________________________________________________________
> _______________________
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).