Terminating Sessions On a Specific Cluster Instance

You can use the ALTER SYSTEM KILL SESSION statement to terminate a session on a specific instance.

When a session is terminated, any session active transactions are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

Using the ALTER SYSTEM KILL SESSION statement enables you to maintain strict application service-level agreements in Oracle RAC environments. Often, the goal of a service-level agreement is to carry out a transaction in a specified time limit. In an Oracle RAC environment, this may require terminating a transaction on an instance, and retrying the transaction on another instance within a specified time frame.

To terminate sessions, follow these steps:

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

2. 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]'

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

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

   • 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.

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

The following examples provide three scenarios in which a user identifies and terminates a specific session. In each example, the SYSDBA first queries the GV$SESSION view for the SCOTT user's session to identify the session to terminate, and then runs the ALTER SYSTEM KILL SESSION statement to terminate the session on the instance.

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';

       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          4          2

SQL> ALTER SYSTEM KILL SESSION '80, 4, @2';
alter system kill session '80, 4, @2'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';
 
       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          6          2
 
SQL> ALTER SYSTEM KILL SESSION '80, 6, @2';

System altered.

SQL> 

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';
 
       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          8          2
 
SQL> ALTER SYSTEM KILL SESSION '80, 8, @2' IMMEDIATE;
 
System altered.
 
SQL>