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

Thread: Kill session on specific node of RAC

  1. #1
    Join Date
    Jun 2007
    Posts
    60

    Kill session on specific node of RAC

    I am trying to kill session on RAC 10g Oracle on the specific instance
    and for some reason it fails. I am on the node 2.

    SQL> ALTER SYSTEM KILL SESSION '119, 35553, @1';
    ALTER SYSTEM KILL SESSION '119, 35553, @1'
    *
    ERROR at line 1:
    ORA-00026: missing or invalid session ID

    If I just run
    SQL> ALTER SYSTEM KILL SESSION '119, 35553'
    2 ;
    ALTER SYSTEM KILL SESSION '119, 35553'
    *
    ERROR at line 1:
    ORA-00030: User session ID does not exist.
    Which is expected .

    According to docs:
    To terminate sessions, follow these steps:

    *

    Query the value of the INST_ID column in the GV$SESSION dynamic performance view to identify which session to terminate
    *

    Issue the ALTER SYSTEM KILL SESSION and specify the session index number (SID) and serial number of a session that you identified with the GV$SESSION dynamic performance view.

    KILL SESSION 'integer1, integer2[, @integer3]'

    o

    For integer1, specify the value of the SID column.
    o

    For integer2, specify the value of the SERIAL# column.
    o

    For the optional integer3, specify the ID of the instance where the session to be killed exists. You can find the instance ID by querying the GV$ tables.

    To use this statement, your instance must have the database open, and your session and the session to be terminated must be on the same instance unless you specify integer3.
    Thanks
    Last edited by zam; 01-29-2008 at 09:41 PM.

  2. #2
    Join Date
    Jun 2007
    Posts
    60

    Additional info

    select inst_id ,sid,serial#, program from gv$session where username='SID_TEST';

    INST_ID SID SERIAL# PROGRAM
    ---------- ---------- ---------- ------------------------------
    1 105 3305 oracle@adl0844 (PZ99)
    1 119 35553 sqlplus@adl0844 (TNS V1-V3)
    2 112 17146 sqlplus@adl0845 (TNS V1-V3)
    2 136 36053 oracle@adl0845 (PZ99)

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what happened when you log directly onto node 1 and do it

  4. #4
    Join Date
    Jun 2007
    Posts
    60
    I did try that and it worked fine.

    On node 1 :

    select sid,serial#,inst_id from gv$session where username ='SID_TEST';

    SID SERIAL# INST_ID
    ---------- ---------- ----------
    135 27786 2
    SQL> alter system kill session '135,27786,@2';
    alter system kill session '135,27786,@2'
    *
    ERROR at line 1:
    ORA-00026: missing or invalid session ID

    Logged on node 2

    SQL> select sid,serial#, program from v$session where username='SID_TEST';

    SID SERIAL# PROGRAM
    ---------- ---------- ------------------------------------------------
    135 27786 sqlplus@adl0845 (TNS V1-V3)

    SQL> alter system kill session '135,27786';

    System altered.

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    I don't believe the session specific kill functionality was available until 11g.

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