Is 'ALTER SYSTEM KILL SESSION' useful?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Is 'ALTER SYSTEM KILL SESSION' useful?

Hybrid View

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Is 'ALTER SYSTEM KILL SESSION' useful?

    After I run ALTER SYSTEM KILL SESSION 'SID, SERIAL#' to kill one session, the session's status is changed to 'KILLED'. I have three questions:
    1.When will the record of this session be removed from V$SESSION? I kill one session several hours ago, but now I can alse find it in V$SESSION.
    2.Should it be kept until the database shutdown?
    3.Are all the resources reserved for this session released?

    BTW, as my Oracle is configed as MTS, those killed session hold my shared servers, just like the following:

    USERNAME STATUS Query Location Disp Disp Status Serv Serv Status
    ------------------------------ -------- ---------------- ---- ---------------- ---- ----------------
    SYS KILLED SERVER D025 WAIT S001 WAIT(RECEIVE)
    SYS KILLED SERVER D014 WAIT S002 WAIT(RECEIVE)
    SYS KILLED SERVER D024 WAIT S000 WAIT(RECEIVE)
    SYS KILLED SERVER D028 WAIT S003 WAIT(RECEIVE)

    select name,status from v$shared_server
    NAME STATUS
    ---- --------------------
    S000 WAIT(RECEIVE)
    S001 WAIT(RECEIVE)
    S002 WAIT(RECEIVE)
    S003 WAIT(RECEIVE)
    S004 WAIT(COMMON)
    S005 WAIT(COMMON)
    S006 WAIT(COMMON)


    And all the first four shared server S000, S001, S002, S003 can not accept new requests, as if their are hung!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    it will be removed from v$session when that user tries to do something in that session. Till then they are still technically connected.

    once they try and do something else, they will get a message saying disconnected and the entry will be removed

  3. #3
    Join Date
    Dec 2001
    Posts
    320
    hi,
    If your platform is windows than read metalink doc. Id: 69882.1 about Orakill.

    PS: Orakill can only be used from the server, you can not use it remotely from a client.

    HTH

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Go through this..
    Note:1023442.6
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  5. #5
    Join Date
    Jun 2003
    Posts
    3
    Originally posted by SANJAY_G
    Go through this..
    Note:1023442.6
    If the count from V$LOCK is zero and the users connection information has not gone away, then the ONLY way this information will be removed is be taking the instance down.

    So, Oracle's answer is that I have to shutdown my instance!

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    A lot of times I have noticed that if the "USER" is running large batch jobs, you have to wait till it is done rolling back the transaction before the session is removed from v$session.

    MH
    I remember when this place was cool.

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