Sunday, November 19, 2006

Display all connected sessions

Sunday, November 19, 2006 Posted by Andre Broers 6 comments
src: http://www.praetoriate.com/oracle_tips_session_details.htm


rem session.sql - displays all connected sessions

set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
--       b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

ttitle off;

spool off;

6 comments: