Killing Sessions in Oracle

There are many reasons why a dba has to kill a session. That is pretty ease but not always successful.

But why it is not always successful?

An alter system kill session ‘<SID>,<SERIAL#>’; may not kill the session because that’s not what the commands does. The command sets a flag in the session which will make the session “commit suicide”.

There are two reasons why a session would “ignore” the flag:

  1. The session realizes the flag and starts undoing the changes (not really ignoring the flag).
  2. The session never checks for the flag which is an abnormal behavior (ignoring the flag).

Let’s look a bit deeper into option 1:

When the session realizes the kill flag, it starts rolling back the open transaction. Normally transactions should be kept small but this is not the standard in production environments. If the transaction is huge this can even take hours.

To see if the session is rolling back you can use the awesome sql script from Tanel Poder (http://blog.tanelpoder.com) http://blog.tanelpoder.com/files/scripts/sw.sql:

SQL> @sw 263
SID     STATE                       EVENT SEQ#    SEC_IN_WAIT P1
------- --------------------------- ------------- ----------- ------
263     WAITING inactive session    140           1584        152

If the session is busy rolling back, you should wait until it is done.

Now we come to option 2:

In the above example the session is idle and not rolling back. This means we safely kill the session’s OS process.

First we have to figure out which process is the correct OS process of this session:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine
      ,p.terminal, s.program
  FROM v$session s, v$process p
 WHERE s.paddr = p.addr
   AND s.sid=:sid
;

Column spid of this query shows the id of the dedicated server. We can now go ahead and kill the process with “kill -9 <spid>”. On Windows systems use orakill %ORACLE_SID% <spid> command (run cmd.exe with administrative privileges).

If the session is still seen in v$sessions you can wake up pmon to clean up the session with the following command from SQL*Plus:

$ sqlplus / as sysdba
SQL> oradebug wakeup <process# of pmon>;

To get the process# of pmon execute the following sql:

SELECT pid
  FROM v$process p, v$bgprocess b
 WHERE b.paddr = p.addr
   AND name = 'PMON'
;

In most cases the process# is 2.

That’s IT

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: