Sessions Details

*  SESSIONS :

col command format a60
set linesize 125
set linesize 1000
clear columns
set pages 10000
alter session set nls_date_format='DD-Mon-YYYY HH24:MI:SS';
col osuser form a10
col username form a10
col command form a10
col sid form "99999"
col machine form a25
col pgram form a15
break on username on osuser on machine skip 1
select s.username,s.osuser,substr(machine,1,25) machine,SID,s.SERIAL#,spid bg_pid,logon_time,s.status,
decode(s.command,0,'',1,'Create Table',2,'Insert',3,'Select',6,'Update',7,'Delete',9,'Create Index',15,'Alter Table',21,'Create View',23,'Validate Index',35,'Alter Database',39,'Create Tablespace',41,'Drop Tablespace',
40,'Alter Tablespace',53,'Drop User',47,'Exec.Procedure',62,'Analyze Table',63,'Analyze Index',s.command||': Other') command
from v$session s,v$process p,v$transaction t where (s.osuser is not null and s.username != 'oracle8i') and
s.paddr = p.addr and s.taddr = t.addr (+)
order by username,osuser,machine,logon_time desc,s.status;

Following script can be used to get information like OS process ID, PGA allocation, SQL_ID, etc. associated with a database session. The script takes SID and SERIAL# as input.

set lines 300;

col "DB User" for a10;

col "OS User" for a10;

col "SQL ID" for a25;

col "OS PID" for a10;

select p.spid as "OS PID",

p.pga_used_mem as "PGA Used",

p.pga_alloc_mem as "PGA Allocated",

p.pga_freeable_mem as "PGA Reclaimable",

s.sql_id as "SQL ID",

s.username as "DB User",

s.status,

s.osuser as "OS User",

s.state "Session State"

from gv$process p

inner join gv$session s

on p.addr=s.paddr

and s.sid=&sid

and s.serial#=&serial

/