No V$ access for developers?
Mark J. Bobak, November 26, 2006 - 8:38 pm UTC
Although, (and I'm surprised you didn't say it, Tom) why a DBA would deny V$ access to developers is beyond me.
-Mark
November 26, 2006 - 9:33 pm UTC
in production - not necessarily a bad idea. guess I was thinking "long term, as part of the application"
then what is this
A reader, November 27, 2006 - 1:20 am UTC
tom i think the following statement gives sessionid
select userenv('sessionid') from dual;
then what is the difference between sessionid and sid
November 27, 2006 - 7:49 am UTC
that is the "audsid"
ops$tkyte%ORA9IR2> select userenv('sessionid'), audsid, sid
2 from v$session
3 where sid = (select sid from v$mystat where rownum=1);
USERENV('SESSIONID') AUDSID SID
-------------------- ---------- ----------
214 214 11
v$mystat not needed
Sokrates, November 27, 2006 - 9:55 am UTC
select sys_context('userenv','sid') from dual where
rownum=1;
also works
you don't need to
select sys_context('userenv','sid') from v$mystat
as proposed by you.
original question was:
how to get session identifier (****without**** using V$ tables).
Query in your answer used v$mystat
November 27, 2006 - 7:30 pm UTC
in 10g.
ops$tkyte%ORA9IR2> select sys_context('userenv','sid') from dual where
2 rownum=1;
select sys_context('userenv','sid') from dual where
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter
See, the poster is using 9ir2, that'll not work for them...
I was demonstrating simply that in 10gr2 - sys_context can be used, it returns the same value as sid from v$mystat.
You did not read my answer I think - I would hope it would be UTTERLY OBVIOUS that you just use sys_context in 10g, but in 9i - no you don't
Sokrates, Not in 9i...
A reader, November 27, 2006 - 11:59 am UTC
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
scott@BDEV:SQL>select sys_context('userenv','sid') from dual ;
select sys_context('userenv','sid') from dual
*
ORA-02003: invalid USERENV parameter
but YES in 10g :
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
SCOTT@o10gr2:SQL>select sys_context('userenv','sid') from dual ;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------
145
SCOTT@o10gr2:SQL>
Regards,
Chiappa
A reader, August 19, 2018 - 6:11 pm UTC
Is there any alternate way to get v$session information without using v$ tables and select any dicitionary priliveges.
And we are using v$session in a procedure and we dont have access to sys user to grant privileges in v_$session. We need to get rid through system user.
Can anyone please help me is there any alternate to get session information.
August 20, 2018 - 3:55 am UTC
It would depend on what you need. You can get *some* information on the *current* session using SYS_CONTEXT (check the manuals for what attributes you can find), but for general access to any session's details...you need v$session
A reader, August 20, 2018 - 6:07 pm UTC
We are using v$session in a procuedre where we dont have access to sys user to grant privilege from sys user and also we dont want to use select any dicitionary privilege, is there any other alternates to access v$session to know the sid and serial# to column values to kill a session.
August 21, 2018 - 11:57 am UTC
Um.... even if you *could* see the SID and the SERIAL#, then if your intention is to kill a session, then that will need to be done as a privileged user anyway.
My Take
Vikas Sangar, July 26, 2019 - 3:49 pm UTC
I know the poster of the question has specified non use of V$ views, but if the access to sys_context is not available then a work-around below (using V$ views) can be helpful...
select /*My Sess*/ 'My Sess Text = hello world' SID_SR_TXT from dual
union
select 'SID = '||sid||' Serial# = '||serial# SID_SR_TXT from v$session where sql_id in
(select sql_id from v$sql where sql_text like '% My Sess %');
SID_SR_TXT
---------------------------------------------
My Sess Text = hello world
SID = 331 Serial# = 19405
2 rows selected.
Hope this is helpful,
Thanks.
My Take
Vikas Sangar, July 26, 2019 - 4:12 pm UTC
One thing I missed to clarify is that for each new session you would need to write and replace the text in first query 'before union' with a unique 'text' following the '=' sign, for Example -
select /*My Sess*/ 'My Sess Text = hello world @ 13/12/2019 12:45:15 PM' SID_SR_TXT from dual
union
select 'SID = '||sid||' Serial# = '||serial# SID_SR_TXT from v$session where sql_id in
(select sql_id from v$sql where sql_text like '% My Sess %');
or, even -
select /*My Sess*/ 'My Sess Text = hello world @ '||systimestamp SID_SR_TXT from dual
union
select 'SID = '||sid||' Serial# = '||serial# SID_SR_TXT from v$session where sql_id in
(select sql_id from v$sql where sql_text like '% My Sess %');
Thanks.
July 29, 2019 - 4:09 pm UTC
I'm not sure how this helps?
My Take
Vikas Sangar, July 31, 2019 - 5:51 pm UTC
Hi. I guess, based on the unique SQL text run within a given session, the SQL would return the SID, Serial# for the session from v$session view (provided access for v$session is given, as mentioned in earlier post).
This is not the best way but works fine.
select /*My Sess*/ 'My Sess Text = hello world @ '||systimestamp SID_SR_TXT from dual
union
select 'SID = '||sid||' Serial# = '||serial# SID_SR_TXT from v$session where sql_id in
(select sql_id from v$sql where sql_text like '% My Sess %');
SID_SR_TXT
--------------------------------------------------------------------------------
My Sess Text = hello world @ 31-JUL-19 01.49.18.743781 PM -04:00
SID = 93 Serial# = 46061
August 01, 2019 - 7:50 am UTC
Yes, but the question was:
"Is there any alternate way to get v$session information without using v$ tables and select any dicitionary priliveges."