Re: Querying current session id
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/~kelfinkReceived on Fri Jul 19 1996 - 00:00:00 CEST