The Geek Diary

How to Termibate a Session on a Specific Instance of Oracle RAC

by

Terminating Sessions on a Specific Instance

You can use the ALTER SYSTEM KILL SESSION statement to terminate a session on a specific instance. The example below illustrates this by terminating a session started on a different instance than the one used to terminate the problematic session.

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

SID         SERIAL#   INST_ID
---------- ---------- ----------
140          3340      2
SQL> ALTER SYSTEM KILL SESSION '140,3340,@2';
System altered.
SQL>

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, Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. 

ALTER SYSTEM KILL SESSION '140,3340,@2'
*
ERROR at line 1:
ORA-00031: session marked for kill

The PMON background process then marks the session as terminated when the activity is complete. The example above assumes that application continuity has not been configured. If application continuity has been configured and you want to terminate the session without being replayed, use the –noreplay option:

sql> alter system kill|disconnect session 'sid, serial#, @inst' noreplay;

If you are terminating sessions in a multitenant environment, determine the container ID (CON_ID) by using show con_id and include it in the GV$SESSION query.

SQL> show con_id
CON_ID
---------
5

How SQL*Plus Commands Affect Instances

SQL*Plus Command Associated Instance
ARCHIVE LOG Generally affects the current instance
CONNECT Affects the default instance if no instance is specified in the CONNECT command
RECOVER Does not affect any particular instance, but rather the database
SHOW PARAMETER and SHOW SGA Show the current instance parameter and SGA information
STARTUP and SHUTDOWN Affect the current instance
SHOW INSTANCE Displays information about the current instance

Most SQL statements affect the current instance. You can use SQL*Plus to start and stop instances in the Oracle RAC database. You do not need to run SQL*Plus commands as root on Linux and UNIX systems or as Administrator on Windows systems. You need only the proper database account with the privileges that you normally use for a non-cluster Oracle database. Some examples of how SQL*Plus commands affect instances are:

  • ALTER SYSTEM CHECKPOINT LOCAL affects the current instance.
  • ALTER SYSTEM CHECKPOINT or ALTER SYSTEM CHECKPOINT GLOBAL affects all instances in the cluster database.
  • ALTER SYSTEM SWITCH LOGFILE affects only the current instance. To force a global log switch, use the ALTER SYSTEM ARCHIVE LOG CURRENT statement. The INSTANCE option of ALTER SYSTEM ARCHIVE LOG enables you to archive each online redo log file for a specific instance
  • The INSTANCE option of ALTER SYSTEM ARCHIVE LOG enables you to archive each online redo log file for a specific instance.

You May Also Like