DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SESSION MARKED FOR KILL!!!!!!!!!!!!!!!!!!!

  1. #1
    Join Date
    Oct 2001
    Location
    Hyderabad
    Posts
    44
    Hi All,
    I got a problem in dropping a user. Let me explain in detail.
    I have to drop a user 'TELCO'.
    I tried to drop the user by connecting to Sysytem user.
    >DROP USER TELCO CASCADE;
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected

    then i querried in V$SESSION for sid,serial#,status and tried to killl the session.
    >ALTER SYSTEM KILL SESSION '16,4558';
    ERROR at line 1:
    ORA-00031: session marked for kill
    This error is because of the process is in UN-INTERRAPTIBLE mode(rollback or network operation...etc)

    My Question is, if it is in process why its status in V$SESSION is showing as KILLED?
    And How to Drop this user?
    Please reply back me ASAP.
    thank you ,
    esreddy.

  2. #2
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    If the session, has submitted a job and Oracle is Processing a request submitted by the
    client process then it cannot kill that session immediately. So, it marks the session as
    Killed and as-soon-as the processing is over, it removes the information about the
    user from V$SESSION view. Until, the record is removed, it means that Oracle has not
    killed the users' session completely.

    Vijay.

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    This has happened often to me too. Try to kill the session/process on OS level.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Oct 2001
    Location
    Hyderabad
    Posts
    44
    Vijay what u said is correct, but in the V$SESSION Table , STATUS of user should not be KILLED, till the process is completed, atleat it should be INACTIVE. Am i correct?
    Originally posted by oravijay
    Hi,

    If the session, has submitted a job and Oracle is Processing a request submitted by the
    client process then it cannot kill that session immediately. So, it marks the session as
    Killed and as-soon-as the processing is over, it removes the information about the
    user from V$SESSION view. Until, the record is removed, it means that Oracle has not
    killed the users' session completely.

    Vijay.

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    As soon as you get the confirmation as 'The session is marked for Killed' it means
    that the user cannot execute subsequent statements(apart from the statements which
    had been already submitted and is currently getting executed). If the user
    try's to execute any other statement he will receive the error "Your
    session has been Killed".
    So I think that the
    message what Oracle gives is correct.

    Vijay.

  6. #6
    Join Date
    Aug 2001
    Posts
    390
    If you want to get rid of this session right away, go to OS and issue the ORAKILL for NT or KILL -9 for Unix to get rid of them, but you need to know the OS number of this session


  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by mike73
    If you want to get rid of this session right away, go to OS and issue the ORAKILL for NT or KILL -9 for Unix to get rid of them, but you need to know the OS number of this session

    Run

    Code:
    select vs.sid, vs.username, vs.osuser, vs.process fg_pid, 
    vp.spid bg_pid 
    from v$session vs, v$process vp 
    where vs.paddr = vp.addr 
    /
    If you get something like:

    SID USERNAME OSUSER FG_PID BG_PID
    ---- --------------- ---------- --------- ---------
    10 JULIAN JULIAN 1965:6969 1234

    run in unix as the oracle user

    kill -9 1234

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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