Source file
set lines 1000 pages 1000
Total Count of sessions
=======================
SQL> select count(status) TOTAL_SESSIONS from gv$session;
TOTAL_SESSIONS
--------------
108
SQL>
Total Count of active/inactive sessions
=======================================
SQL> select count(s.status) INACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';
INACTIVE_SESSIONS
-----------------
44
SQL> select count(s.status) ACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='ACTIVE';
ACTIVE_SESSIONS
---------------
63
SQL> select count(s.status) ACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='ACTIVE';
ACTIVE_SESSIONS
-----------------
63
SQL>
SQL> select SID, SERIAL#,USERNAME,STATUS,STATE,MACHINE,LOGON_TIME from v$session;
SID SERIAL# USERNAME STATUS STATE MACHINE LOGON_TIM
---------- ---------- -------------------- -------- ------------------- -------------------- ---------
1 39392 SYSRAC INACTIVE WAITING ol79.bracecoder 25-MAY-25
2 40049 ACTIVE WAITING ol79.bracecoder 25-MAY-25
3 19146 ACTIVE WAITING ol79.bracecoder 25-MAY-25
4 43725 ACTIVE WAITING ol79.bracecoder 25-MAY-25
5 16500 ACTIVE WAITING ol79.bracecoder 25-MAY-25
6 2080 ACTIVE WAITING ol79.bracecoder 25-MAY-25
7 50854 ACTIVE WAITING ol79.bracecoder 25-MAY-25
8 55744 SOE INACTIVE WAITING Muthu 25-MAY-25
9 1736 ACTIVE WAITING ol79.bracecoder 25-MAY-25
10 13816 ACTIVE WAITING ol79.bracecoder 25-MAY-25
11 16753 ACTIVE WAITING ol79.bracecoder 25-MAY-25
12 57671 ACTIVE WAITING ol79.bracecoder 25-MAY-25
13 30705 SYS ACTIVE WAITING ol79.bracecoder 25-MAY-25
14 3195 ACTIVE WAITING ol79.bracecoder 25-MAY-25
15 4181 ACTIVE WAITING ol79.bracecoder 25-MAY-25
16 29273 ACTIVE WAITING ol79.bracecoder 25-MAY-25
17 8609 ACTIVE WAITING ol79.bracecoder 25-MAY-25
18 13940 ACTIVE WAITING ol79.bracecoder 25-MAY-25
19 43436 ACTIVE WAITING ol79.bracecoder 25-MAY-25
20 21430 ACTIVE WAITING ol79.bracecoder 25-MAY-25
21 29100 ACTIVE WAITING ol79.bracecoder 25-MAY-25
22 21955 ACTIVE WAITING ol79.bracecoder 25-MAY-25
23 32934 ACTIVE WAITING ol79.bracecoder 25-MAY-25
24 27021 ACTIVE WAITING ol79.bracecoder 25-MAY-25
25 5917 ACTIVE WAITING ol79.bracecoder 25-MAY-25
27 48752 ACTIVE WAITING ol79.bracecoder 25-MAY-25
28 9841 ACTIVE WAITING ol79.bracecoder 25-MAY-25
30 33714 ACTIVE WAITING ol79.bracecoder 25-MAY-25
31 14498 ACTIVE WAITING ol79.bracecoder 25-MAY-25
32 15416 ACTIVE WAITING ol79.bracecoder 25-MAY-25
33 30918 ACTIVE WAITING ol79.bracecoder 25-MAY-25
34 41539 ACTIVE WAITING ol79.bracecoder 25-MAY-25
35 49451 ACTIVE WAITING ol79.bracecoder 25-MAY-25
36 8913 ACTIVE WAITING ol79.bracecoder 25-MAY-25
39 65367 ACTIVE WAITING ol79.bracecoder 25-MAY-25
43 49268 SOE INACTIVE WAITING Muthu 25-MAY-25
44 459 SOE INACTIVE WAITING Muthu 25-MAY-25
45 45473 SOE INACTIVE WAITING Muthu 25-MAY-25
46 47054 SOE INACTIVE WAITING Muthu 25-MAY-25
48 39772 ACTIVE WAITING ol79.bracecoder 25-MAY-25
49 61300 ACTIVE WAITING ol79.bracecoder 25-MAY-25
50 45775 ACTIVE WAITING ol79.bracecoder 25-MAY-25
52 56546 ACTIVE WAITING ol79.bracecoder 25-MAY-25
54 54123 ACTIVE WAITING ol79.bracecoder 25-MAY-25
56 29923 ACTIVE WAITING ol79.bracecoder 25-MAY-25
59 48703 ACTIVE WAITING ol79.bracecoder 25-MAY-25
61 27104 ACTIVE WAITING ol79.bracecoder 25-MAY-25
66 64539 SOE INACTIVE WAITING Muthu 25-MAY-25
67 41490 ACTIVE WAITING ol79.bracecoder 25-MAY-25
68 51470 SYS ACTIVE WAITED SHORT TIME ol79.bracecoder 25-MAY-25
69 46213 ACTIVE WAITING ol79.bracecoder 25-MAY-25
71 22650 ACTIVE WAITING ol79.bracecoder 25-MAY-25
72 6759 SOE INACTIVE WAITING Muthu 25-MAY-25
73 5889 ACTIVE WAITING ol79.bracecoder 25-MAY-25
75 1333 SOE INACTIVE WAITING Muthu 25-MAY-25
78 17417 ACTIVE WAITING ol79.bracecoder 25-MAY-25
82 328 ACTIVE WAITING ol79.bracecoder 25-MAY-25
86 54728 SOE INACTIVE WAITING Muthu 25-MAY-25
90 2946 SOE INACTIVE WAITING Muthu 25-MAY-25
94 43715 SOE ACTIVE WAITING Muthu 25-MAY-25
96 47161 ACTIVE WAITING ol79.bracecoder 25-MAY-25
103 13059 SOE INACTIVE WAITING Muthu 25-MAY-25
106 63521 ACTIVE WAITING ol79.bracecoder 25-MAY-25
111 6562 ACTIVE WAITING ol79.bracecoder 25-MAY-25
116 12104 SOE INACTIVE WAITING Muthu 25-MAY-25
118 42679 SOE INACTIVE WAITING Muthu 25-MAY-25
120 37314 ACTIVE WAITING ol79.bracecoder 25-MAY-25
124 57288 SOE ACTIVE WAITING Muthu 25-MAY-25
125 62520 ACTIVE WAITING ol79.bracecoder 25-MAY-25
126 42628 SOE INACTIVE WAITING Muthu 25-MAY-25
127 31716 SOE INACTIVE WAITING Muthu 25-MAY-25
130 62446 ACTIVE WAITING ol79.bracecoder 25-MAY-25
133 25569 ACTIVE WAITING ol79.bracecoder 25-MAY-25
135 8782 ACTIVE WAITING ol79.bracecoder 25-MAY-25
137 1349 ACTIVE WAITING ol79.bracecoder 25-MAY-25
139 63356 ACTIVE WAITING ol79.bracecoder 25-MAY-25
141 7889 SOE INACTIVE WAITING Muthu 25-MAY-25
142 23184 SOE INACTIVE WAITING Muthu 25-MAY-25
143 10547 SOE INACTIVE WAITING Muthu 25-MAY-25
144 38036 ACTIVE WAITING ol79.bracecoder 25-MAY-25
146 29451 ACTIVE WAITING ol79.bracecoder 25-MAY-25
148 20530 SOE ACTIVE WAITING Muthu 25-MAY-25
149 52911 SOE ACTIVE WAITING Muthu 25-MAY-25
150 9816 SOE INACTIVE WAITING Muthu 25-MAY-25
151 16630 SOE INACTIVE WAITING Muthu 25-MAY-25
152 1004 SOE INACTIVE WAITING Muthu 25-MAY-25
153 7424 SOE INACTIVE WAITING Muthu 25-MAY-25
154 38897 SOE INACTIVE WAITING Muthu 25-MAY-25
155 50005 SOE INACTIVE WAITING Muthu 25-MAY-25
156 56541 SOE INACTIVE WAITING Muthu 25-MAY-25
157 2365 SOE INACTIVE WAITING Muthu 25-MAY-25
159 60440 SOE INACTIVE WAITING Muthu 25-MAY-25
160 21697 SOE ACTIVE WAITING Muthu 25-MAY-25
163 26580 SOE INACTIVE WAITING Muthu 25-MAY-25
164 19090 SOE INACTIVE WAITING Muthu 25-MAY-25
165 44028 SOE INACTIVE WAITING Muthu 25-MAY-25
166 36842 SOE ACTIVE WAITING Muthu 25-MAY-25
167 16966 SOE INACTIVE WAITING Muthu 25-MAY-25
169 3554 SOE INACTIVE WAITING Muthu 25-MAY-25
170 9588 SOE INACTIVE WAITING Muthu 25-MAY-25
171 37287 SOE INACTIVE WAITING Muthu 25-MAY-25
172 12797 SOE INACTIVE WAITING Muthu 25-MAY-25
173 2407 SOE INACTIVE WAITING Muthu 25-MAY-25
174 49083 SOE INACTIVE WAITING Muthu 25-MAY-25
175 64948 SOE INACTIVE WAITING Muthu 25-MAY-25
178 42374 SOE INACTIVE WAITING Muthu 25-MAY-25
179 65248 SOE INACTIVE WAITING Muthu 25-MAY-25
181 25428 SOE INACTIVE WAITING Muthu 25-MAY-25
182 21871 SOE INACTIVE WAITING Muthu 25-MAY-25
109 rows selected.
SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR
==========================================================
SQL> select count(s.status) "INACTIVE SESSIONS > 1HOUR " from gv$session s, v$process p where p.addr=s.paddr and s.last_call_et > 3600 and s.status='INACTIVE';
INACTIVE SESSIONS > 1HOUR
--------------------------
1
SQL>
TOTAL SESSIONS COUNT ORDERED BY PROGRAM
=======================================
SQL>
SQL> col program for a40
select s.program,count(s.program) Total_Sessions from gv$session s, v$process p where p.addr=s.paddr group by s.program;
PROGRAM TOTAL_SESSIONS
---------------------------------------- --------------
oracle@ol79.bracecoder (VKTM) 1
oracle@ol79.bracecoder (CKPT) 1
oracle@ol79.bracecoder (SMON) 1
oracle@ol79.bracecoder (RECO) 1
oracle@ol79.bracecoder (RBAL) 1
oracle@ol79.bracecoder (TMON) 1
oracle@ol79.bracecoder (M003) 1
oracle@ol79.bracecoder (W003) 1
oracle@ol79.bracecoder (PSP0) 1
oracle@ol79.bracecoder (SCMN) 2
oracle@ol79.bracecoder (LREG) 1
oracle@ol79.bracecoder (PXMN) 1
oracle@ol79.bracecoder (Q003) 1
oracle@ol79.bracecoder (CJQ0) 1
sqlplus@ol79.bracecoder (TNS V1-V3) 1
oracle@ol79.bracecoder (Q00Q) 1
oracle@ol79.bracecoder (W007) 1
oracle@ol79.bracecoder (GEN0) 1
oracle@ol79.bracecoder (M004) 1
oracle@ol79.bracecoder (VKRM) 1
oracle@ol79.bracecoder (W001) 1
JDBC Thin Client 48
oracle@ol79.bracecoder (CLMN) 1
oracle@ol79.bracecoder (SVCB) 1
oracle@ol79.bracecoder (LGWR) 1
oracle@ol79.bracecoder (W004) 1
oracle@ol79.bracecoder (AQPC) 1
oracle@ol79.bracecoder (QM02) 1
oracle@ol79.bracecoder (DIAG) 1
oracle@ol79.bracecoder (DIA0) 1
oraagent.bin@ol79.bracecoder (TNS V1-V3) 1
oracle@ol79.bracecoder (M000) 1
oracle@ol79.bracecoder (QM00) 1
oracle@ol79.bracecoder (Q00G) 1
oracle@ol79.bracecoder (PMAN) 1
oracle@ol79.bracecoder (W000) 1
oracle@ol79.bracecoder (MMON) 1
oracle@ol79.bracecoder (MMNL) 1
oracle@ol79.bracecoder (TT02) 1
oracle@ol79.bracecoder (W005) 1
oracle@ol79.bracecoder (Q00K) 1
oracle@ol79.bracecoder (Q00S) 1
oracle@ol79.bracecoder (Q00T) 1
oracle@ol79.bracecoder (MMAN) 1
oracle@ol79.bracecoder (GEN1) 1
oracle@ol79.bracecoder (OFSD) 1
oracle@ol79.bracecoder (DBRM) 1
oracle@ol79.bracecoder (DBW0) 1
oracle@ol79.bracecoder (ASMB) 1
oracle@ol79.bracecoder (MARK) 1
oracle@ol79.bracecoder (M005) 1
oracle@ol79.bracecoder (TT00) 1
oracle@ol79.bracecoder (TT01) 1
oracle@ol79.bracecoder (Q00O) 1
oracle@ol79.bracecoder (Q00R) 1
oracle@ol79.bracecoder (PMON) 1
oracle@ol79.bracecoder (SMCO) 1
oracle@ol79.bracecoder (FENC) 1
oracle@ol79.bracecoder (W002) 1
oracle@ol79.bracecoder (W006) 1
60 rows selected.
SQL>
TOTAL COUNT OF SESSIONS ORDERED BY MODULE
=========================================
col module for a40
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions from gv$session s, v$process p where p.addr=s.paddr group by s.module;
MODULE TOTAL_SESSIONS
---------------------------------------- --------------
sqlplus@ol79.bracecoder (TNS V1-V3) 1
KTSJ 9
JDBC Thin Client 48
Streams 10
36
oraagent.bin@ol79.bracecoder (TNS V1-V3) 1
MMON_SLAVE 3
7 rows selected.
SQL>
TOTAL COUNT OF SESSIONS ORDERED BY ACTION
=========================================
SQL> col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions from gv$session s, v$process p where p.addr=s.paddr group by s.action;
ACTION TOTAL_SESSIONS
------------------------------ --------------
KTSJ Slave 8
Intensive AutoTask Dispatcher 1
EMON Coordinator 1
Cleanup KGL Handle Action 1
KTSJ Coordinator 1
QMON Coordinator 1
EMON Regular Slave 4
85
Monitor FRA Space 1
EMON Reliable Slave 1
Monitor Tablespace Thresholds 1
QMON Slave 3
12 rows selected.
SQL>
INACTIVE SESSIONS
=================
prompt INACTIVE SESSIONS
col ACTION for a10
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';
SPID SID LAST_CALL_ET STATUS ACTION MODULE PROGRAM
------------------------ ---------- ------------ -------- ---------- ---------------------------------------- ----------------------------------------
4412 1 1.52472222 INACTIVE oraagent.bin@ol79.bracecoder (TNS V1-V3) oraagent.bin@ol79.bracecoder (TNS V1-V3)
17749 116 0 INACTIVE JDBC Thin Client JDBC Thin Client
17752 43 0 INACTIVE JDBC Thin Client JDBC Thin Client
17754 44 0 INACTIVE JDBC Thin Client JDBC Thin Client
17756 181 0 INACTIVE JDBC Thin Client JDBC Thin Client
18230 104 0 INACTIVE JDBC Thin Client JDBC Thin Client
17758 151 0 INACTIVE JDBC Thin Client JDBC Thin Client
17762 170 0 INACTIVE JDBC Thin Client JDBC Thin Client
17764 94 0 INACTIVE JDBC Thin Client JDBC Thin Client
17766 148 0 INACTIVE JDBC Thin Client JDBC Thin Client
17768 149 0 INACTIVE JDBC Thin Client JDBC Thin Client
17772 166 0 INACTIVE JDBC Thin Client JDBC Thin Client
17774 178 0 INACTIVE JDBC Thin Client JDBC Thin Client
17777 124 0 INACTIVE JDBC Thin Client JDBC Thin Client
17779 141 0 INACTIVE JDBC Thin Client JDBC Thin Client
17781 90 0 INACTIVE JDBC Thin Client JDBC Thin Client
17783 179 0 INACTIVE JDBC Thin Client JDBC Thin Client
17785 143 0 INACTIVE JDBC Thin Client JDBC Thin Client
17789 142 0 INACTIVE JDBC Thin Client JDBC Thin Client
17791 154 0 INACTIVE JDBC Thin Client JDBC Thin Client
17793 173 0 INACTIVE JDBC Thin Client JDBC Thin Client
17800 86 0 INACTIVE JDBC Thin Client JDBC Thin Client
17802 46 0 INACTIVE JDBC Thin Client JDBC Thin Client
17804 163 0 INACTIVE JDBC Thin Client JDBC Thin Client
17806 45 0 INACTIVE JDBC Thin Client JDBC Thin Client
17809 126 0 INACTIVE JDBC Thin Client JDBC Thin Client
17811 172 0 INACTIVE JDBC Thin Client JDBC Thin Client
17813 171 0 INACTIVE JDBC Thin Client JDBC Thin Client
17816 118 0 INACTIVE JDBC Thin Client JDBC Thin Client
17821 103 0 INACTIVE JDBC Thin Client JDBC Thin Client
17819 169 0 INACTIVE JDBC Thin Client JDBC Thin Client
17823 72 0 INACTIVE JDBC Thin Client JDBC Thin Client
17828 152 0 INACTIVE JDBC Thin Client JDBC Thin Client
17830 164 0 INACTIVE JDBC Thin Client JDBC Thin Client
17832 8 0 INACTIVE JDBC Thin Client JDBC Thin Client
17835 66 0 INACTIVE JDBC Thin Client JDBC Thin Client
17837 174 0 INACTIVE JDBC Thin Client JDBC Thin Client
17839 75 0 INACTIVE JDBC Thin Client JDBC Thin Client
17841 167 0 INACTIVE JDBC Thin Client JDBC Thin Client
17845 175 0 INACTIVE JDBC Thin Client JDBC Thin Client
17847 153 0 INACTIVE JDBC Thin Client JDBC Thin Client
17849 165 0 INACTIVE JDBC Thin Client JDBC Thin Client
17852 150 0 INACTIVE JDBC Thin Client JDBC Thin Client
17857 160 0 INACTIVE JDBC Thin Client JDBC Thin Client
17860 127 0 INACTIVE JDBC Thin Client JDBC Thin Client
17865 156 0 INACTIVE JDBC Thin Client JDBC Thin Client
17867 159 0 INACTIVE JDBC Thin Client JDBC Thin Client
47 rows selected.
SQL>
Kill sessions
=============
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' immediate;
SQL> alter system kill session '182,21871' immediate;
System altered.
SQL> alter system kill session '182,21871' immediate;
alter system kill session '182,21871' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
SQL>
