Re: Getting own session id

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/19
Message-ID: <8ilha3$nso$1@nnrp1.deja.com>#1/1

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