Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    4

    Red face Unanswered: kill session does not work

    Hi all,
    I met a problem on oracle.
    I use "alter system kill session",killed a session.The session is marked "killed",but exists in database for TWO days.The unix procesess id is in v$session,but in fact the process does not exist any more.
    So how can I kill this session?I can not restart DB server because of production system.This session locks some tables.
    Thanks first.

    Regards,
    Jacky

  2. #2
    Join Date
    Apr 2003
    Location
    Germany
    Posts
    25
    Hi
    your session is probaby a pl/sql-program-unit w2orking in a loop.
    The only way i think would be to build in a check in this program if it should stop.
    it could query a config-table which you could update with information to stop and leave the progam unit.

    so far, if somebody has another idea then please answer
    orca

  3. #3
    Join Date
    May 2003
    Posts
    4
    Hi,
    I have stopped the program related.
    And I used "alter system kill session" and "alter system disconnect session",both will not work.

    Regards,
    Jacky Huang

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by jacky huang
    Hi,
    I have stopped the program related.
    And I used "alter system kill session" and "alter system disconnect session",both will not work.

    Regards,
    Jacky Huang
    have you set resource limit in you init.ora ?

    the Oracle stragegy for eliminating killed sessions is not always obvious...
    my experience shows that they reside always a couple of days in v$session depending on the number of processes in init.ora, but with the deendency I'm not quite sure

  5. #5
    Join Date
    May 2003
    Posts
    4
    Hi,
    I would like not to restart db server.How can I do? The killed session
    locked some table.
    Help!!!

    Regards,
    Jacky

  6. #6
    Join Date
    Apr 2003
    Location
    Germany
    Posts
    25
    hi,
    did you try orakill, an executable to kill oracle sessions from os-side?
    Orca

  7. #7
    Join Date
    May 2003
    Posts
    4
    My Oracle is on solaris,and oracle is 9iR2.
    No orakill found.

  8. #8
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Since you have Solaris, use this shell script to kill the sessions


    #!/bin/sh

    tmpfile=/tmp/tmp.$$
    sqlplus system/manager <<EOF
    spool $tmpfile
    select p.spid from v\$process p,v\$session s where s.paddr=p.addr and s.status='KILLED';
    EOF

    for x in `cat $tmpfile | grep "^[0123456789]"`
    do
    kill -9 $x
    done
    rm $tmpfile


    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

  9. #9
    Join Date
    May 2003
    Posts
    3
    The OS process is gone, so I don't see how any OS tool will help. It sounds like the session is busy waiting for a system resource preventing it from accepting the kill status. This is usually caused by a long rollback, but since your session has been stale for several days, it is unlikely your problem.

    You may try to walk back through the code (join to v$sqlarea with sql_address in v$session) and see if there is any code that the session is stuck waiting for system resources. I wouldn't know exactly what to look for myself, but maybe check for any smtp or dbms_pipe references etc..

    Or of course, this could be an Oracle bug. :-( Check technet.oracle.com

    Good Luck!

  10. #10
    Join Date
    May 2003
    Posts
    12
    This might be obvious but I have noticed that when you kill session (from outside Oracle, using SQL) it may take up to 2 minutes before it vanishes from the active session list. Also Oracle Metalink is a good choice to look fo information if you have registered into it.
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

Posting Permissions

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