Session ID

[jestep]

How can I get the SessionID from Oracle
If the connected user does not have access to v$session??

 

[benthicsoftware]

Here's a query with some of the user information including the session id:

select
user "Username",
uid "User ID",
userenv('ENTRYID') "Entry ID?",
userenv('SESSIONID') "Session ID",
userenv('TERMINAL') "Terminal",
userenv('LANGUAGE') "Language"
from
dual;

These should work for anyone.

Good luck!

------------------
Mark Ford
Benthic Software www.benthicsoftware.com
Quality Oracle Tools

[This message has been edited by benthicsoftware (edited 24 January 2001).]

 

[jestep]

Thanks for the response but,
userenv('SESSIONID') "Session ID" actually gives you the AUDSID not the SID from v$session. I need the SID....

 

[benthicsoftware]

Ah, that's a horse of a different color!

Unless someone has a better way, here's something:

Create a function in a schema that does have rights to v$session that returns the sid:

create or replace function BS_GETSID
return number as
vsid number(38);
cursor c1 is
select sid from v$session where audsid = USERENV('SESSIONID');
begin
open c1;
fetch c1 into vsid;
close c1;
return vsid;
end;

grant execute rights to the user or to public and then run it like:

select sys.bs_getsid() sid from dual;

Best I can do, I'm afraid! Perhaps someone is going to point out a much better solution!

 

[jestep]

I hope there's a better solution....

Thanks for the help anyway...

 

[Joachim Rupik]

If you don't want to grant select on V$SESSION, you can grant select on V$MYSTAT
(This table keeps current session statistics)
and select SID using:

select distinct SID from V$MYSTAT

Joachim Rupik