Re: Querying current session id

From: Kevin Fries <kelfink_at_ecst.csuchico.edu>
Date: 1996/07/19
Message-ID: <4socmu$4kk_at_charnel.ecst.csuchico.edu>#1/1


In article <31ef859d.978214_at_n5.gbso.net>, Chuck Hamilton <chuckh_at_dvol.com> wrote:
>I want to run a query that shows the comlete currently executing SQL
>statement for everyone logged into the database, except for the
>session that's running this query. I need to know my own session ID
>and/or serial # for this. How can I find that out?
>
>I can't do it by username because the user running the query might
>also be running other querys that I do want to see.
>--
>Chuck Hamilton
>chuckh_at_dvol.com

User the "userenv" function:
  userenv('SESSIONID')

When you select from v$session, compare

  WHERE audsid <> userenv('sessionid')

to exclude your own session.

The following is part of a query I use to see who's on my database.

  • sqlplus script: **

set feedback off
set pages 200
set lines 79
set head off
column kill_id format A8 word_wrap
column kill_id head "Kill ID"
column program head Program
column orauser head "DB login"
column terminal head "Locale"
column orauser format A9
column program format A16 word_wrap
column terminal format A9
column Activity format A20

select ' * connections to Oracle database '||name from sys.v_$database; set head on

SELECT substr(s.sid||','||s.serial#,1,10) kill_id,   substr(s.username,1,10) orauser,
  decode(substr(s.osuser,1,10),'daemon','<netv1>',

        'OraUser','<net>',
	substr(s.osuser,1,8)) os_user,

 substr(decode(s.machine,'unknown','',NULL,'',substr(s.machine,1,10))||

    decode(s.terminal,'Windows PC','???',s.terminal),1,9) terminal,   decode(sign(length(s.program) - 15),-1,   s.program, substr(s.program,length(s.program)-15) ) program,   substr(substr(MODULE,1,6)||'.'||ACTION,1,20) Activity  FROM v$session s, v$process p
 WHERE s.paddr = p.addr

    and audsid <> userenv('sessionid')
   and background is null
 ORDER BY 4
/
exit

Hope this helps,
Kevin Fries

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink
Received on Fri Jul 19 1996 - 00:00:00 CEST

Original text of this message