Purpose
This script reports information about the current user’s session. It should not be used when connected as user SYS. This is because audsid for user SYS as well as background processes is always 0 and, therefore, the script will return multiple entries in this case.
Configuration
Access Privileges
Requires SELECT privilege on v$process and v$session or SELECT_CATALOG_ROLE(Oracle8 and higher).
Usage
Copy the script to a file named tfssgrep.sql and execute it from SQL*Plus connected as the user you wish to report on.
sqlplus username/ @tftssinfo.sql
Script
def aps_prog = 'mysess.sql'
def aps_title = 'Session Info'
col "Session Info" form A80
select 'Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| 'DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| 'Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
'OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| chr(10)||
'Client Program Name : '||s.program "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and s.audsid = userenv('SESSIONID')
/
Sample Output
Session Info -------------------------------------------------------------------------------- Sid, Serial#, Aud sid : 7 , 79 , 412 DB User / OS User : SCOTT / asantana Machine - Terminal : stsun7 - pts/98 OS Process Ids : 18820 (Client) 18831 (Server) Client Program Name : sqlplus@stsun7 (TNS V1-V2)