Kill Oracle Session

There are many reasons why you might want to kill an Oracle database session. Perhaps there are a large number of sessions connected from an application server which is malfunctioning and you want to remove them all. Maybe you want to put the database in to a restricted session state and kill off a load of the users. Or, more common, a user’s session is stuck or doing something which you don’t want it to so you kill it.

You use the statement ALTER SYSTEM KILL SESSION ‘<sid>,<serial#>’ IMMEDIATE; to kill an Oracle session, where <sid> and <serial#> are taken from the V$SESSION view.

FROM v$session
WHERE username = 'TEST';

Dynamic SQL to Kill Oracle Sessions

Using dynamic SQL you can easily kill any group of connected sessions to the database. Here is an example of how to do that:

This first query will help you to see what machines have sessions which are connected to the Oracle database:

SELECT machine, count(*)
FROM v$session
GROUP BY machine
ORDER BY machine;

This next dynamic SQL statement will generate the kill session statements for you to run, and filter by the machine.

SELECT 'alter system kill session '''||sid||','||serial#||''' immediate;'
FROM v$session
WHERE machine = '<machine_name>';

The user will receive an error message on their screen (if they are logged in directly through SQL Plus)

ORA-00028: your session has been killed

Sometimes you will find that the session you have attempted to kill does not go, but has a status of ‘KILLED’ in the database. From my experience there are two reasons for this:

      1) The session was executing some long running insert/update/delete statement which was not committing as it went through, which means that it has to rollback all of the work done before the session is removed. You can find out more information about how to get the database to rollback faster in my other article, under the section “Rollback Faster”.

Or you will just have to be patient and wait for the database to finish rolling back the data if you cannot restart the database instance.

      2) The session just gets stuck, for some unknown reason. In this case you can look at using the ORAKILL utility, which we look at below.


Note: ORAKILL is a Windows utility. If you are running on a Linux system you can look at the KILL command, and I believe KILL -9 is the most forceful.

First of all we need to find out the SPID of the process that we want to kill. For that we can use the following query, where we put the username in. You could use any column in v$session that you want to filter based on.

SELECT ses.username, pr.spid
FROM v$session ses, v$process pr
WHERE ses.paddr = pr.addr
AND ses.username = 'USER1';

The next step is to use the ORAKILL utility, which is found in you BIN directory within the Oracle home. The syntax for the command is as follows:

orakill <instance_name> <spid>

Where instance_name is the name of your oracle service running on the Windows server and spid is the number obtained from the klonopin clonazepam klonopin database using the query above. Here is an example of how you would run it:

orakill oracle 123

You should get a message stating that the command was executed successfully. If you are running on a Windows platform the tasklist command in my other article may be of interest to you for finding out which process in Task Manager is which Oracle service.

Like it, share it...

Category: Articles

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *