Monday, 30 July 2012

Terminating sessions on a specific RAC database instance

As a DBA, we typically use the 'ALTER SYSTEM KILL SESSION' statement to terminate (kill) any specific session on the database, and if the session is active and involved with any operations, we tend to get the 'ORA-00031: session marked for kill' and the session won't be terminated until the outstanding activity complete.

I then learned about the IMMEDIATE (ALTER SYSTEM KILL SESSION ...... IMMEDIATE') clause that most probably terminates the session without actually waiting for the outstanding activity to complete.  In addition, it also records the information (SID,PID) with the database alert.log file.
Today, I come across about terminating sessions on a specific RAC database instance in Oracle 11gR2 (I am not sure about Oracle 11gR1). This will give the flexibility to terminate a session on a specific RAC database instance across cluster environment. The following is the syntax and example of the enhancement:

Syntax 
KILL SESSION 'sid, serial#[, @integer3]'
 
Example
ALTER SYSTEM KILL SESSION '1287, 9823, @2' IMMEDIATE; 
 

No comments:

Post a Comment