cancel
Showing results for 
Search instead for 
Did you mean: 

Connection SID (Oracle)

SOLVED
Go to solution
Solrac
Occasional Contributor

Connection SID (Oracle)

I am trying to retrieve the Session ID of the current database connection. I have searched in the V$SESSION table, but there are several SID's asociated to my username. Is there a way to resolve which one of them is the one who belongs to the current connection?

Thanks in advance.

System: HP 9000
OS: HP-UX 11i
DataBase: Oracle 8.1.7
6 REPLIES
mark spoke
Occasional Visitor
Solution

Re: Connection SID (Oracle)

You may try to execute:
select distinct sid from v$mystat;
la vita l'e' bela basta aver l'umbrela
Christian Gebhardt
Honored Contributor

Re: Connection SID (Oracle)

Hi
maybe you find your connection with the terminal column of v$session.

in sqlplus:
SQL> host env | grep -i pts
SSH_TTY=/dev/pts/11
SQL> select sid,terminal from v$session;
16 pts/11

Another possible way is the timestamp of the unix-process
SQL> select PROCESS from v$session;
SQL> host ps -ef | grep

Chris
Indira Aramandla
Honored Contributor

Re: Connection SID (Oracle)

You can select the sid, serial#, username, osuser, logon_time from v$session and this will give you the time of the various logins and the operating system as whome you logged on. But if you have OS authenticated user, then the logon_time value should give you your session information.
OR

You can select from V$session and v$process where v$process will have the terminal number of the PC that you are running from. Refer to the attachment for the script. Hope this helps.
Never give up, Keep Trying
Graham Cameron_1
Honored Contributor

Re: Connection SID (Oracle)

A better way, which doesn't require access to the catalog tables, is to use SYS_CONTEXT, as follows:

select SYS_CONTEXT('USERENV','SESSIONID')
from dual ;

There are many other values which SYS_CONTEXT can return, see the Oracle SQL reference guide.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Steven E. Protter
Exalted Contributor

Re: Connection SID (Oracle)

select * from v$instance;

Tells you what instance you have which is set by the variable ORACLE_SID in most cases.

each instance has its own v$session table which tracks who's logged on and what they are doing.

set your sid and then check v$session

My dba has a policy. He runs that first sql statement right after connect internal to make sure whatever he's about to do is going to be to the instnace he thinks he's about to do it to.

If you know what I mean.

Here is a link to a thread filled with wonderful sql scripts for all kinds of database tasks.

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x4177ef70e827d711abdc0090277a778c,00.html


SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com

Re: Connection SID (Oracle)

Hi !
v$mystat helps You. This view contains sid of current session.