Inactive sessions are not expiring/closing.
Hi,
In my database there is no activity right now.
But sessions are not closing and cursors created by them are not releasing.Here is and output of this query.
SQL> select sid,count(hash_value) from v$open_cursor group by sid order by count(hash_value);
SID COUNT(HASH_VALUE)
---- -----------------
196 1
22 4
221 63
229 66
140 67
187 67
238 68
118 70
130 70
173 70
142 70
135 72
190 73
119 75
93 76
115 76
178 78
199 82
200 82
418 93
292 104
45 108
363 201
358 203
61 213
347 214
431 215
100 222
120 223
332 238
The sessions 332 ,120 and others have more than 200 cursors open in database which is not expected as there is no activity in the database.
The output of this query is also unexpected.
select sid, username, server, program, type, machine, logon_time, status from v$session
SID USERNAME SERVER PROGRAM TYPE MACHINE LOGON_TIM STATUS
---- --------------- --------- ----------------------------------- ---------- -------------------- --------- --------
1 DEDICATED oracle@cu2.dns77.com (PMON) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
2 DEDICATED oracle@cu2.dns77.com (DIAG) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
3 DEDICATED oracle@cu2.dns77.com (LMON) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
4 DEDICATED oracle@cu2.dns77.com (LMD0) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
5 DEDICATED oracle@cu2.dns77.com (LMS0) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
7 DEDICATED oracle@cu2.dns77.com (LMS1) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
9 DEDICATED oracle@cu2.dns77.com (DBW0) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
10 DEDICATED oracle@cu2.dns77.com (DBW1) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
11 DEDICATED oracle@cu2.dns77.com (LGWR) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
12 DEDICATED oracle@cu2.dns77.com (CKPT) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
13 DEDICATED oracle@cu2.dns77.com (SMON) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
14 DEDICATED oracle@cu2.dns77.com (RECO) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
15 DEDICATED oracle@cu2.dns77.com (CJQ0) USER cu2.dns77.com 04-OCT-05 ACTIVE
16 DEDICATED oracle@cu2.dns77.com (ARC0) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
17 DEDICATED oracle@cu2.dns77.com (ARC1) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
20 DEDICATED oracle@cu2.dns77.com (LCK0) BACKGROUND cu2.dns77.com 04-OCT-05 ACTIVE
45 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
61 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
93 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
100 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
115 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
118 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
119 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
120 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
130 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
135 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
140 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
142 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
173 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
178 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
187 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
190 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
196 SYS DEDICATED sqlplus@cu2.dns77.com (TNS V1-V3) USER cu2.dns77.com 04-OCT-05 ACTIVE
199 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
200 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
221 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
229 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
238 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
292 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
332 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
347 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
358 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
363 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
418 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
431 BIDRXDV1 NONE USER cu4.dns77.com 04-OCT-05 INACTIVE
So many inactive session remaining live which should not happen.
Can anybody say why this is happening?
