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@usscora300 (PMON) 1625534
2 oracle@usscora300 (DBW0) 1625534
3 oracle@usscora300 (DBW1) 1625534
4 oracle@usscora300 (DBW2) 1625534
5 oracle@usscora300 (DBW3) 1625534
6 oracle@usscora300 (LGWR) 1625534
7 oracle@usscora300 (CKPT) 1625534
8 oracle@usscora300 (SMON) 1625534
9 oracle@usscora300 (RECO) 1625534
15 oracle@usscora300 (ARC0) 1625534
377 oracle@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:[EMAIL PROTECTED]]
> 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
>
> >>> [EMAIL PROTECTED] 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
>
> <[EMAIL PROTECTED] To: Multiple
> recipients of
> list
> ORACLE-L <[EMAIL PROTECTED]>
> .za> cc:
>
> Sent by: Subject: Finding User
> sessions
> = idle > 30 min??
> root@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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
