SESSION QUERIES

------------------------------------------------------------------------------------------------------
col USERNAME format a10;
col MACHINE format a40;
col OSUSER format a10;
col PROCESS format a10;
set linesize 200;

select SID,serial#,username,MACHINE,OSUSER,process from v$session where username='DBSNMP' and status='INACTIVE';

select SID,serial#,username,MACHINE,OSUSER,process from v$session where username='DBSNMP' and status='ACTIVE';

------------------------------------------------------------------------------------------------------

col sid format 999999

col username format a20

col osuser format a15

select b.spid,a.sid, a.serial#,a.username, a.osuser

from v$session a, v$process b

where a.paddr= b.addr

and b.spid='&spid'

order by b.spid;

------------------------------------------------------------------------------------------------------

select p.spid,s.action,s.sid,s.

serial#,s.username,s.status, s.sql_hash_value,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser

from V$process p,V$session s

where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%'

AND SPID=&OS_PID;

------------------------------------------------------------------------------------------------------

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='XXX' and status='INACTIVE';

------------------------------------------------------------------------------------------------------

Get Details for SID

set lines 200 pages 5000

col program for a25

col event for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,

to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,

v$session_wait b where a.sid=&sid and a.sid=b.sid;

------------------------------------------------------------------------------------------------------

To See active session of users in a dB;

select USERNAME,count(*) from v$session where status='ACTIVE' GROUP BY USERNAME;

------------------------------------------------------------------------------------------------------