Killing Sessions in Oracle Database

There's no running away from this... you have to kill sessions at some point while managing Oracle databases. A database session is essentially a user process spawned as a result of a user connections to the database i.e. a session is  created when a user connects to the database. When a session is terminated/killed, active transactions from the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

Terminating the wrong session can be destructive if the wrong session is killed, you could end up killing a wrong session that was not intended to be killed or removing a background process which could lead to instance crash/abort (I've made this error in the past - It was not a funny incident).

Several reasons that can warrant this drastic action (as the name suggests) are highlighted below.

1.You might want to perform an administrative operation and need to terminate all non-administrative user sessions

2. Wait events can be resolved by killing the blocking session which holding up the resource(s)

3. User sessions can be killed to enable batch processes run faster

4. You can use the technique to remove long running queries either to rewrite the query or run at another time when the system is less busy

Identifying sessions to be killed

You can identify session to be killed by querying V$SESSION and V$PROCESS. Database sessions can be marked for kill by selecting based on USERNAME, MACHINE, PROGRAM, OS_USER or STATUS. Sessions STATUS can be any of the following states;

• ACTIVE - Session currently executing SQL
• INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
• KILLED - Session marked to be killed
• CACHED - Session temporarily cached for use by Oracle*XA
• SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.

To get more familiar with V$SESSION and V$PROCESS run a describe on each table.

Identifying sessions for kill in a RAC database has a little twist to it. The GV$SESSION and GV$PROCESS dynamic performace views can be queried from any instance to get database wide session data. Whiile instance specific session data can be gotten by querying our good old V$SESSION & V$PROCESS from each instance.

Killing the sessions

To kill sessions at the database level, Oracle provides the alter system kill statement. Captions below demonstrate how to kill a user session;

1. Connect to the database with any named user

SQL> conn kehinde

Enter password:

Connected.

SQL> select sysdate from dual;

SYSDATE

-----------

16-OCT-2015

SQL>

2. In another session login as sysdba to kill the user session created above

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

     SID   SERIAL#

-------- ---------

    2273       145

           

SQL> alter system kill session '2273,145';

System altered.

SQL>

3. Go back to user session and try to run any script

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-00028: your session has been killed

                                                                  

For RAC databases, ensure that the kill script for instance a run instance a session as you might end up terminating a session you did not intend to terminate if the script is run on another instance.

To kill multiple sessions, say all inactive sessions on the server you can use script below to genrate the kill script and run as a batch;

Script

SELECT NVL(s.username, '(oracle)') AS username,

       s.osuser,

       'alter system kill session '''||s.sid||','||s.serial#||'''',

       'kill -9 '||p.spid,

       s.sid,

       s.serial#,

       p.spid,

       s.lockwait,

       s.status,

       s.module,

       s.machine,

       s.program,

       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM   v$session s, v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY s.osuser;

For unix systems you can also kill sessions from the operating system level by using the command kill -9 spid, this is also selected as part of the script above. Alternatively you can run ps -ef | grep LOCAL from the OS to identify process ids to be killed. Note that you need to be very careful while selecting processes to be killed with this command. I will show the pitfalls and how to avoid them in the next post which will be on "Startup & Shutdown - Bouncing the Database". 

Hope this helps!

Kehinde.