sp_who.sql

/* ####################################################################### */
/*                                                                         */
/* 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;