Alter system kill session doesn't releases locks, but killing on OS release it, WHY??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Alter system kill session doesn't releases locks, but killing on OS release it, WHY??

  1. #1
    Join Date
    Nov 2000
    Posts
    224
    Oracle 805, OS-Unix
    Some of my sessions were waiting due to locks on table, I tried to kill the session using
    SQL> alter system kill session '11,1234';
    Checking the status in V$SESSION shows KILLED and never goes out from V$SESSION. WHY?????

    The objects locked by killed session keeps holding the locks on the objects for long time.
    When killing the session from OS (kill -9 processid) releases the locks and V$SESSION also doen't shows that session any more.

    Why it is so that killing at OS level cleaned the things which were not cleaned by alter system kill session?
    Why not use OS kill command always to kill the session? Is there any disadvantage in doing so?

  2. #2
    Join Date
    Feb 2001
    Posts
    27

    Hi Sharma,

    If the user session which you are going to kill did lot of works and used a lot of resources (rollback, redo, ..) will take an enormous amount of time to kill that process from database by issuing 'Alter system kill session .....' command

    The reason is that this command first marks the session for killing and is not killed untill all the resources and locks are released.

    But if you use operating system command (kill -9 pid), it will directly kill the session without releasing the locks and resources. In this case PMON process is responsible to release the resources later on.

    Both the cases, you are fine - no worries.


    Thanks
    Babu

  3. #3
    Join Date
    Nov 2000
    Posts
    224
    When I checked all the rollback were complete but the locks held never released. The locks were released when the process was killed from OS.

    I learned that the entry in V$SESSION will remain there till client knows it has been killed.

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by Sharma
    The locks were released when the process was killed from OS.
    Are you sure? I dont think the killed session holds lock on any of the objects. It may take sometime to release the lock.

    And, yes.. The session will be marked as "killed" in v$session until that user queries his session or the database is bounced.

    -nagarjuna

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