session.sql

Rem SID = Session identifier
Rem SER = Session serial number
Rem OSUER = Operating system username
Rem OSPID = Operating system process identifier
Rem STAT = Status of session (ACT=active INA=Inactive)
Rem COM = Command number.  Definition listed in table Audit_actions
Rem SCHEMA = Oracle username
Rem TYP = Type of process (USE=user BAC=background)
Rem %HIT = Hit ratio in percent
Rem CPU = CPU being used
Rem BCHNG = Block changes
Rem CCHNG = Consistent changes
Rem
set lines 120;
select substr(s.sid,1,3)||','||substr(s.serial#,1,5) SID_SER,
substr(osuser,1,5) osuser,spid ospid,
substr(status,1,3) stat,substr(command,1,3) com,
substr(schemaname,1,10) schema,
substr(type,1,3) typ,
substr(decode((consistent_gets+block_gets),0,'None',
(100*(consistent_gets+block_gets-physical_reads)/
  (consistent_gets+block_gets))
),1,4) "%HIT",value CPU,
--substr(block_changes,1,5) bchng,
--substr(consistent_changes,1,5) cchng,
SUBSTR(s.program,1,30) PROGRAM,
SUBSTR(S.MACHINE,1,20) MACHINE
from v$process p, v$SESSTAT t,v$sess_io i ,v$session s
where i.sid=s.sid  and p.addr=paddr(+) and s.sid=t.sid and
t.statistic#=12
order by schemaname, s.program, osuser
/
select 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual
/