/* ####################################################################### */
/* */
/* FILENAME: sp_who.sql */
/* */
/* Purpose: Shows who is in the database */
/* */
/* Parameters: None */
/* */
/* *** DO NOT HAND EDIT THIS FILE *** */
/* ####################################################################### */
/* */
/* Revision History: */
/* */
/* REV DATE BY DESCRIPTION */
/* --- ------ -------------- ------------------------------------------ */
/* 1.0 INFRASTRUCTURE Initial Relase */
/* */
/* *** DO NOT HAND EDIT THIS FILE *** */
/* */
/* ####################################################################### */
set pages 30 head off feed off
select 'User Status for Oracle Database is ', name from v\$database;
set head on
prompt
prompt All currently running processes
prompt ===============================
set pages 60 lines 132
col sid format 999 heading "SID"
col Typ format a3
col process format a10 heading "Client PID"
col spid format a10 heading "Server PID"
col username format a10 heading "ORA User"
col osuser format a8 heading "OS User"
col term format a7 heading "TTY"
col stat format a2 heading "ST"
col lw format a2 heading "LK"
col com format a8 heading "Command"
col mach format a10 heading "Host"
col latchw format a2 heading "LA"
select s.sid,
s.serial#,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
s.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(nvl(substr(s.terminal,1,7),'?'),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT','IDLE'),'?') com,
substr(nvl(s.machine,'?'),1,9) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is NULL
union
select s.sid,
s.serial#,
nvl(decode(s.sql_hash_value,0,' ','*'),'?')||nvl(s.process,'?') process,
nvl(p.spid,'?') spid,
p.username username, nvl(substr(s.osuser,1,8),'?') osuser,
replace(replace(substr(p.terminal,1,7),'PC',''),'tty','') term,
decode(s.status,'ACTIVE','R','INACTIVE','W','KILLED','Z') stat,
decode(s.lockwait,NULL,'N','Y') lw,
nvl(decode(s.command,1,'CRT TABL',2,'INSERT',3,'SELECT',4,'CRT CLST',
5,'ALT CLST',6,'UPDATE',7,'DELETE',8,'DROP',9,'CRT INDX',
10,'DRP INDX',11,'ALT INDX',12,'DRP TABL',15,'ALT TABL',
17,'GRANT',18,'REVOKE',19,'CRT SYNM',20,'DRP SYNM',21,
'CRT VIEW',22,'DRP VIEW',26,'LOC TBLE',27,'NOTHING',28,
'RENAME',29,'COMMENT',30,'AUDIT',31,'NOAUDIT',32,'CRT XTDB',
33,'DRP XTDB',34,'CRT DBSE',35,'ALT DBSE',36,'CRT RSEG',
37,'ALT RSEG',38,'DRP RSEG',39,'CRT TBSP',40,'ALT TBSP',
41,'DRP TBSP',42,'ALT SESS',43,'ALT USER',44,'COMMIT',
45,'ROLLBACK',46,'SVEPOINT'),'?') com,
substr(nvl(s.machine,'?'),1,9) mach,
decode(p.latchwait,NULL,'N','Y') latchw,
decode(least(instr(p.program,'TCP'),1),1,'TCP',
decode(least(instr(p.program,'TNS'),1),1,'TNS',
decode(least(instr(p.program,'Pip'),1),1,'Pip',' '))) Type
from v\$session s, v\$process p
where addr = paddr
and p.background is not NULL
order by 1;
prompt
prompt Rollback Segment Usage
prompt ======================
set lines 150
col name format a10
col usn format 99
col sid format 999
col xacts format 9999
col extents format 999999
col extends format 99999
col waits format 9999
col wraps format 9999999
col shrnk format 999999
col name format a12
col osusr format a6
col status format a9
col writes format 9999999999999
select r.usn, r.name, s.osuser osusr,
s.sid, x.extents, x.xacts,
x.extends, x.waits, x.shrinks shrnk,
x.wraps, x.writes, x.status
from v\$rollstat x,
v\$rollname r,
v\$session s,
v\$transaction t
where t.addr = s.taddr (+)
and x.usn (+) = r.usn
and t.xidusn (+) = r.usn
order by r.usn;
exit;