Inactive sessions are not expiring/closing.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Inactive sessions are not expiring/closing.

Hybrid View

  1. #1
    Join Date
    Jun 2005
    Location
    India
    Posts
    14

    Arrow 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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are you useing shared server?

    and anyway shy shouldnt there be inactive sessions in the databse people do log in and not log out sometimes

  3. #3
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    DO you have a profile assigned to users to limit session time etc.?
    If you kill the inactive sessions (lets say running for 20 min) the cursor space will be released?
    Only a suggestion
    Able was I ere I saw Elba

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width