Re: Getting own session id

To find SID and SERIAL# of your own session use the following:

select sid, serial#
from v$session
where audsid = userenv('sessionid');

You don't need to enable Audit in order to use the audsid column and the method is generic for all platforms.

Cheers,
Jesper

On Mon, 19 Jun 2000 16:21:35 GMT, ddf_dba_at_my-deja.com wrote:

>In article <8gmve6$97i$1_at_news.xmission.com>,
> "Hello" <hello_at_hello.world> wrote:
>> Is there any way to find out the session id or serial# for the current
>> session, v$session is fine but I don't know of a way to identify the
 current
>> session itself, most of the times it is a lucky guess.
>>
>> Thanks
>>
>>
>
>Presuming you're on UNIX you can isolate the current session based upon
>the value in the TERMINAL column. UNIX has a command named tty that
>will provide the terminal information. Let us say we have a SQL*Plus
>script named get_curr_sess.sql and it will accept a value for the
>TERMINAL comparison. From the UNIX command prompt you would type:
>
>sqlplus user/password @get_curr_sess `tty`
>
>The `tty` actually executes the tty command so that the value would be
>passed into the script. The SQL*Plus script would look something like
>this:
>
>rem "$Header: /usr/users/davidf/tuning/user/RCS/get_curr_sess.sql,v 1.1
>2000/03/24 15:56:48 davidf Exp $"
>rem
>rem get_curr_sess.sql
>rem
>rem Display selected session information
>rem for the current session
>rem
>rem Flags inactive sessions
>rem
>
>set serveroutput on echo off feedback off verify off
>spool ?/tuning/reports/user_sess
>declare
> curr_sid v$session.sid%type;
> ora_uname v$session.username%type;
> u_user v$session.osuser%type;
> trmnal v$session.terminal%type;
> prgm v$session.program%type;
> sts v$session.status%type;
> lckwt v$session.lockwait%type;
> ptype v$session.type%type;
> serial v$session.serial#%type;
> cursor get_session_info is
> select sid, username, osuser, terminal, program,
> status,lockwait, type, serial#
> from v$session
> where type = 'USER'
> and '/dev/'||terminal = '&&1'
> and paddr in(
> select addr
> from v$process)
> order by status;
>begin
> dbms_output.enable(1000000);
> open get_session_info;
> loop
> fetch get_session_info into curr_sid, ora_uname, u_user, trmnal,
>prgm,
> sts, lckwt, ptype, serial;
> exit when get_session_info%notfound;
>
>dbms_output.put_line('==================================================
>===========');
> dbms_output.put_line('Oracle Session ID: '||curr_sid);
> dbms_output.put_line('Oracle Serial No : '||serial);
> dbms_output.put_line('Oracle User Name : '||ora_uname);
> dbms_output.put_line('OS User Name : '||u_user);
> dbms_output.put_line('Terminal : '||trmnal);
> dbms_output.put_line('Program : '||prgm);
> if sts = 'INACTIVE' then
> dbms_output.put_line('Status : **** INACTIVE ****');
> else
> dbms_output.put_line('Status : '||sts);
> end if;
> dbms_output.put_line('Lock Wait Status : '||lckwt);
> dbms_output.put_line('Process Type : '||ptype);
> end loop;
>
>dbms_output.put_line('==================================================
>===========');
>end;
>/
>spool off
>
>You could also have a Bourne shell script to do all of this for you:
>
>#!/bin/sh
>#
># get_curr_sess
>#
># Get current session information from the database
>#
># ddf
>
># Set up the environment to access Oracle
>
>. $HOME/.profile
>
># Stuff we need
>
>SCRIPT="$ORACLE_HOME/tuning/user/get_curr_sess"
>TTY=`tty`
>USER="user/password"
>SQL=$ORACLE_HOME/bin/sqlplus
>
># Go to it
>
>$SQL $USER $SCRIPT $TTY
>
>Something similar can be done with batch files and the terminal name in
>NT since that information will populate the TERMINAL column for
>Windows-based applications.
>
>David Fitzjarrell
>Oracle Certified DBA
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Jun 19 2000 - 00:00:00 CDT