Oracle not releasing processes killed couple of weeks ago
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Oracle not releasing processes killed couple of weeks ago

Hybrid View

  1. #1
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Hi,

    I killed few user sessions couple of weeks ago, Oracle show the session status as 'Killed' and never release those processes. I can't see session process id (spid) if I run the query against v$session and v$process. Whats going on here? could somebody explain ?

    Bouncing database is not an option for me to clean held up processes and kinda resources are limited on these box so some how I need to clean these processes. How ?

    Environment:
    Solaris 5.8
    Oracle 8172
    Reddy,Sam

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    There are a couple of things you can do. First, check if they are sessions that might have been using a database link. If so, you have to kill the remote session in order for them to go away.

    If that's not the case, you can always kill the process from the OS side. Use something like
    Code:
    set pages 50
    col kill_by for a12
    col username for a12
    col program for a40 
    col "MACHINE/PID" for a20
    set linesize 132
    set pages 100
    select '''' || s.sid ||','||s.serial# || '''' kill_by, 
       p.spid server_pid, s.username, s.program, 
       decode(s.process, NULL, s.machine, s.machine || ' PID: ' ||  s.process) "MACHINE/PID" ,
       s.server, s.status
    from v$session s, v$process p
    where s.username is not null
    and s.paddr = p.addr
    order by to_number(p.spid)
    to find the OS Process id. Then from your OS, issue kill -HUP . If your process doesn't die in a few minutes, issue kill -KILL . It might take a couple of minutes for the entry to be removed from v$session.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Jeff,

    Thanks much for input. Those processes are not dblinks are something but, another dba laptop sessions which were held up from first week of April which I killed sometime in May. Last night we got error of Max processes exceeded. Today I see those processes sitting in Killed status. Its Weird. I ran the query which show up session processes and it doesn't show up any processes held from that machine/user. Even the query you gave me doesn't list those processes.

    To my understanding there is no process held up from OS side for that user but oracle side there is something goofy going on...

    Any ideas ?
    Reddy,Sam

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmmm, I had a problem like this but using connection managers. The client process would go away but still would show up in v$session. We just bounced the cman to clear everything up.

    Are you using MTS or dedicated servers? If MTS, you could down one of the dispatchers, but that would also blow out anybody that has a valid connection.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    It is possible that the killed session is not doing any processing currently, in that case the query might not
    list it. Why dont you just kill the process from OS??
    Sridhar R Patnam

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Jeff,

    Its not MTS enabled. The total number of processes held by Oracle from couple of weeks are 22. Resources are limited to only 200 processes. Now and then Iam hitting Max Processes exceeded...

    I'm thinking of now! even if I go and kill the low priority user sessions to give priority for Application sessions Iam not gonna buy anything as Oracle never release those processes. So apparently killing current processes doesn't help much either...
    Reddy,Sam

  7. #7
    Join Date
    Feb 2001
    Posts
    290
    Reddy,
    I am interested to know the final soultion , which you have given to this problem, cause i ahve seen this manay times and i end up bouncing the DB.

    Madhu Reddy
    xdollor@yahoo.com

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    It's a very common and weird sight but if users are using different tools to access the db, even after they are killed or after being killed and asked to login to finally complete the killing (since the session was inactive) by executing and allowing it to complete.And then closing the application. Still I've found very weird(I got into an argument with the SA i remember because he thought I was messing around) that the process kept running on the client machine even though the application was closed.
    Maybe this will throw in some light and help you further.

    Cheers!

    Tarry
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Originally posted by Tarry
    the process kept running on the client machine even though the application was closed.
    Tarry
    Thats not possible because its another co-dba laptop and he shutdown and start it everyday whenever he wants to use. Iam thinking of open a tar and see whats oracle solution for this.
    Reddy,Sam

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    This is what Oracle explaination for killed sessions not being removed from v$session view and holding the session process and getting ORA-00020 Max processes exceeded error (when you have limited processes that you can configure on the server).

    When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

    PMON will not delete the session object itself until the client connected to that session notices that it has been killed Therefore, the sequence of events is:

    1) alter system kill session is issued - the STATUS of the session object in V$SESSION becomes KILLED, its server becomes PSEUDO.
    2) PMON cleans up the *resources* allocated to the session (i.e., rolls back its transaction, releases its locks, etc).
    3) the entry in V$SESSION remains there until the client of that session tries to do another request.
    (the client is the process associated with the OSUSER,MACHINE,PROCESS columns in the V$SESSION view)
    4) the client attempts another SQL statement and gets back ORA-28.
    5) PMON can now remove the entry from V$SESSION.

    Oracle says: this behavior is necessary because the client still has pointers to the session object even though the session has been killed. Therefore, the object cannot be deleted until the client is no longer pointing at it.

    The onlyway to get rid of these killed sessions with holding resources is by BOUNCING THE DATABSE. No one thinks killing sessions (not couple, but many) might get the situation of bouncing the database... That's the way Oracle works...

    [Edited by sreddy on 06-13-2002 at 11:54 AM]
    Reddy,Sam

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