Oracle Shell Scripts for active session on unix

active session info on unix prompt


sh psora.sh
====================


##!/bin/sh
# Script Name: psora
export ORACLE_HOME=/opt/app/oracle/product/11.2
export ORACLE_SID=orcl
sqlplus / as sysdba <set pagesize 30
set heading off
set feedback off
select 'Database is ', name from v\$database;
set heading on
prompt
prompt All currently running processes
prompt ===============================
set pagesize 60
column sid format 999 heading "SID"
column Typ format a3
column process format a9 heading "Clnt PID"
column spid format a5 heading "Serv PID"
column username format a10 heading "Ora User"
column osuser format a8 heading "OS User"
column term format a7 heading "tty"
column stat format a2 heading "ST"
column lw format a2 heading "LK"
column com format a8 heading "Command"
column mach format a12 heading "Host"
column latchw format a2 heading "LA"
select s.sid,
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,12) 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,
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,12) 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 ======================
column uname format a30
column usn format 99
column sid format 999
column xacts format 9999
column extents format 999999
column extends format 99999
column waits format 9999
column wraps format 9999
column shrnk format 9999
column name format a12
column osusr format a6
SELECT r.usn, r.name uname, s.osuser osusr, s.sid, x.extents, x.xacts, x.extends, x.waits, x.shrinks shrnk, x.wraps, x.writes
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;
eof1


===============================================================================



SID Clnt PID Serv Ora User OS User tty ST LK Command Host LA TYP
---- --------- ----- ---------- -------- ------- -- -- -------- ------------ -- ---
2 10163 10163 oracle oracle UNKNOWN R N ? apt-amd-02 N
3 10165 10165 oracle oracle UNKNOWN R N ? apt-amd-02 N
4 10169 10169 oracle oracle UNKNOWN R N ? apt-amd-02 N
5 10171 10171 oracle oracle UNKNOWN R N ? apt-amd-02 N
6 10173 10173 oracle oracle UNKNOWN R N ? apt-amd-02 N
7 10175 10175 oracle oracle UNKNOWN R N ? apt-amd-02 N
8 10177 10177 oracle oracle UNKNOWN R N ? apt-amd-02 N
9 10179 10179 oracle oracle UNKNOWN R N ? apt-amd-02 N
10 10181 10181 oracle oracle UNKNOWN R N ? apt-amd-02 N
11 10183 10183 oracle oracle UNKNOWN R N ? apt-amd-02 N
12 10185 10185 oracle oracle UNKNOWN R N ? apt-amd-02 N
13 10187 10187 oracle oracle UNKNOWN R N ? apt-amd-02 N
14 10189 10189 oracle oracle UNKNOWN R N ? apt-amd-02 N
15 10191 10191 oracle oracle UNKNOWN R N ? apt-amd-02 N
16 10193 10193 oracle oracle UNKNOWN R N ? apt-amd-02 N
18 10257 10257 oracle oracle UNKNOWN R N ? apt-amd-02 N
20 10263 10263 oracle oracle UNKNOWN R N ? apt-amd-02 N
21 10265 10265 oracle oracle UNKNOWN R N ? apt-amd-02 N
22 10267 10267 oracle oracle UNKNOWN R N ? apt-amd-02 N
27 10301 10301 oracle oracle UNKNOWN R N ? apt-amd-02 N
31 10321 10321 oracle oracle UNKNOWN R N ? apt-amd-02 N
32 *19999 20000 SYS oracle ? R N SELECT apt-amd-02 N TNS
35 10329 10329 oracle oracle UNKNOWN R N ? apt-amd-02 N
36 10323 10323 oracle oracle UNKNOWN R N ? apt-amd-02 N
39 10731 10731 oracle oracle UNKNOWN R N ? apt-amd-02 N
50 19564 19564 oracle oracle UNKNOWN R N ? apt-amd-02 N
SQL>

SQL> Rollback Segment Usage

SQL> ======================


USN UNAME OSUSR SID EXTENTS XACTS EXTENDS WAITS SHRNK WRAPS WRITES
--- ------------------------------ ------ ---- ------- ----- ------- ----- ----- ----- ----------
0 SYSTEM 6 0 0 0 0 0 5864
21 _SYSSMU21_1694229119$ 3 0 50 3 17 104 37428688
22 _SYSSMU22_1205666354$ 3 0 43 6 18 113 40558478
23 _SYSSMU23_3748597963$ 3 0 62 2 17 119 59366818
24 _SYSSMU24_2622248712$ 3 0 44 4 16 98 41052052
25 _SYSSMU25_4160302927$ 4 0 47 4 19 109 33633816
26 _SYSSMU26_98525116$ 4 0 101 5 22 200 37865310
27 _SYSSMU27_1850131278$ 3 0 61 4 19 122 48728572
28 _SYSSMU28_855357226$ 5 0 65 4 19 119 41685458
29 _SYSSMU29_2316658341$ 3 0 83 8 21 153 51136230
30 _SYSSMU30_3387177988$ 3 0 58 6 16 115 41442164