Alter Syste kill session ... DOESN'T KILLS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Alter Syste kill session ... DOESN'T KILLS

  1. #1
    Join Date
    Nov 2000
    Posts
    224
    I am getting the error, Maximum connection reached.

    I set the PROCESS parameter to 200. Can I set it higher than this?

    Also, when I try to kill some of the session using,

    Alter System Kill Session 'sid,serial#);

    I still see the same no. of records from V$SESSION table, also, it shows same sid & serial# in querying V$SESSION.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Sharma
    I am getting the error, Maximum connection reached.

    I set the PROCESS parameter to 200. Can I set it higher than this?

    Also, when I try to kill some of the session using,

    Alter System Kill Session 'sid,serial#);

    I still see the same no. of records from V$SESSION table, also, it shows same sid & serial# in querying V$SESSION.

    What error number are you getting?

    When killing a session, look at the STATUS column of v$session. If it says 'KILLED', then the session is in the process of rolling the current transaction back and will die eventually.
    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
    Jun 2001
    Location
    Charlotte, NC
    Posts
    27
    Originally posted by Sharma
    I am getting the error, Maximum connection reached.

    I set the PROCESS parameter to 200. Can I set it higher than this?

    Also, when I try to kill some of the session using,

    Alter System Kill Session 'sid,serial#);

    I still see the same no. of records from V$SESSION table, also, it shows same sid & serial# in querying V$SESSION.

    You may want to try implementing MTS mode

  4. #4
    Join Date
    Oct 2000
    Posts
    76
    I have had experience in 8.1.6 on solaris- When a session is killed, and status is marked as killed, the process would 'stuck' in the rollback state forever-even for a simple select statement with nothing to rollback. If I try to kill it again after three days, it still says it was 'marked for kill'. We had to bounce the database to clean it up - or if we can't, we would have to rename the procedure involved and run the new one. Any suggestions as to how to kill these processes permantly?
    J.T.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That could be a bug. What is your DB version in full?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Oct 2000
    Posts
    76
    Hi, Sam-

    the version we have is:

    SQL> select * from v$version
    2 /

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
    PL/SQL Release 8.1.6.1.0 - Production
    CORE 8.1.6.0.0 Production
    TNS for Solaris: Version 8.1.6.0.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production


    J.T.

  7. #7
    Join Date
    Jun 2000
    Location
    Chennai/India
    Posts
    90
    Hi,
    I've come across this situation several times, the way I use to solve is just issuing the combination of "ALTER SYSTEM CHECKPOINT" and "ALTER SYSTEM FLUSH SHARED_POOL" several times repeatedly. I've also experienced the state like HUNG, but then eventually this could solve.
    A Winner never Quits, A Quitter never Wins
    Ganesh .R

  8. #8
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    I've seen that also. It is a normal behaviour for Oracle to rollback the transaction being killed before freeing the ressources, nevertheless it sometimes stays stuck ... what I used to do was to kill -9 the client process (OracleSID) corresponding to the session that was killed

  9. #9
    Join Date
    Oct 2000
    Posts
    76
    Ganish-

    How many time did you have to issue the alter system commends for it to work? I did it three times already (but within 10 minutes) and I still can not kill the process that's been there for like a week.

    Pipo-

    How do you find out the unix sid for that particular oracle process? If I do

    ps -ef|grep ORACLE_SID

    it won't find the sid number for the oracle process, i.e. the sid from v$session.

    Thanks for the help!
    J.T.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jt
    How do you find out the unix sid for that particular oracle process?
    Look at the server_pid column from the following script:
    Code:
    set pages 50
    col kill_by for a10
    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: ' ||  to_char(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)
    /
    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."

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