topic Re: Connection SID (Oracle) in Operating System - HP-UX

Connection SID (Oracle)

Solrac — Wed, 19 Feb 2003 10:23:00 GMT

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

Re: Connection SID (Oracle)

mark spoke — Wed, 19 Feb 2003 10:39:07 GMT

You may try to execute:
select distinct sid from v$mystat;

Re: Connection SID (Oracle)

Christian Gebhardt — Wed, 19 Feb 2003 10:45:57 GMT

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

Re: Connection SID (Oracle)

Indira Aramandla — Thu, 20 Feb 2003 05:00:33 GMT

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.

Re: Connection SID (Oracle)

Graham Cameron_1 — Thu, 20 Feb 2003 08:17:20 GMT

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

Re: Connection SID (Oracle)

Steven E. Protter — Fri, 21 Feb 2003 19:12:47 GMT

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

Re: Connection SID (Oracle)

Evgeny Filippenkov — Sat, 22 Feb 2003 06:38:00 GMT

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