How to kill a running SELECT statement

Question

How can I stop a running SELECT statement by killing the session?

The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Answer 1

As you keep getting pages of results I'm assuming you started the session in SQL*Plus. If so, the easy thing to do is to bash ctrl + break many, many times until it stops.

The more complicated and the more generic way(s) I detail below in order of increasing ferocity / evil. The first one will probably work for you but if it doesn't you can keep moving down the list.

Most of these are not recommended and can have unintended consequences.

1. Oracle level - Kill the process in the database

As per ObiWanKenobi's answer and the ALTER SESSION documentation

alter system kill session 'sid,serial#'; 

To find the sid, session id, and the serial#, serial number, run the following query - summarised from OracleBase - and find your session:

select s.sid, s.serial#, p.spid, s.username, s.schemaname      , s.program, s.terminal, s.osuser   from v$session s   join v$process p     on s.paddr = p.addr  where s.type != 'BACKGROUND' 

If you're running a RAC then you need to change this slightly to take into account the multiple instances, inst_id is what identifies them:

select s.inst_id, s.sid, s.serial#, p.spid, s.username      , s.schemaname, s.program, s.terminal, s.osuser   from Gv$session s   join Gv$process p     on s.paddr = p.addr    and s.inst_id = p.inst_id  where s.type != 'BACKGROUND' 

This query would also work if you're not running a RAC.

If you're using a tool like PL/SQL Developer then the sessions window will also help you find it.

For a slightly stronger "kill" you can specify the IMMEDIATE keyword, which instructs the database to not wait for the transaction to complete:

alter system kill session 'sid,serial#' immediate; 

2. OS level - Issue a SIGTERM

kill pid 

This assumes you're using Linux or another *nix variant. A SIGTERM is a terminate signal from the operating system to the specific process asking it to stop running. It tries to let the process terminate gracefully.

Getting this wrong could result in you terminating essential OS processes so be careful when typing.

You can find the pid, process id, by running the following query, which'll also tell you useful information like the terminal the process is running from and the username that's running it so you can ensure you pick the correct one.

select p.*   from v$process p   left outer join v$session s     on p.addr = s.paddr  where s.sid = ?    and s.serial# = ? 

Once again, if you're running a RAC you need to change this slightly to:

select p.*   from Gv$process p   left outer join Gv$session s     on p.addr = s.paddr  where s.sid = ?    and s.serial# = ? 

Changing the where clause to where s.status = 'KILLED' will help you find already killed process that are still "running".

3. OS - Issue a SIGKILL

kill -9 pid 

Using the same pid you picked up in 2, a SIGKILL is a signal from the operating system to a specific process that causes the process to terminate immediately. Once again be careful when typing.

This should rarely be necessary. If you were doing DML or DDL it will stop any rollback being processed and may make it difficult to recover the database to a consistent state in the event of failure.

All the remaining options will kill all sessions and result in your database - and in the case of 6 and 7 server as well - becoming unavailable. They should only be used if absolutely necessary...

4. Oracle - Shutdown the database

shutdown immediate 

This is actually politer than a SIGKILL, though obviously it acts on all processes in the database rather than your specific process. It's always good to be polite to your database.

Shutting down the database should only be done with the consent of your DBA, if you have one. It's nice to tell the people who use the database as well.

It closes the database, terminating all sessions and does a rollback on all uncommitted transactions. It can take a while if you have large uncommitted transactions that need to be rolled back.

5. Oracle - Shutdown the database ( the less nice way )

shutdown abort 

This is approximately the same as a SIGKILL, though once again on all processes in the database. It's a signal to the database to stop everything immediately and die - a hard crash. It terminates all sessions and does no rollback; because of this it can mean that the database takes longer to startup again. Despite the incendiary language a shutdown abort isn't pure evil and can normally be used safely.

As before inform people the relevant people first.

6. OS - Reboot the server

reboot 

Obviously, this not only stops the database but the server as well so use with caution and with the consent of your sysadmins in addition to the DBAs, developers, clients and users.

7. OS - The last stage

I've had reboot not work... Once you've reached this stage you better hope you're using a VM. We ended up deleting it...

Answer 2

This is what I use. I do this first query to find the sessions and the users:

select s.sid, s.serial#, p.spid, s.username, s.schemaname      , s.program, s.terminal, s.osuser   from v$session s   join v$process p     on s.paddr = p.addr  where s.type != 'BACKGROUND'; 

This will let me know if there are multiple sessions for the same user. Then I usually check to verify if a session is blocking the database.

SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION, COMMAND, MODULE, STATUS FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;   

Then I run an ALTER statement to kill a specific session in this format:

ALTER SYSTEM KILL SESSION 'sid,serial#';  

For example:

ALTER SYSTEM KILL SESSION '314, 2643';