Re: Getting own session id

try looking at the DBMS_SESSION.UNIQUE_SESSION_ID function :)

HTH, David.

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 Tue Jun 20 2000 - 00:00:00 CDT