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';

Answer 3

To kill a session in Oracle, you use the ALTER SYSTEM KILL SESSION command.

Details here: http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Answer 4

There is no need to kill entire session. In Oracle 18c you could use ALTER SYSTEM CANCEL:

Cancelling a SQL Statement in a Session

You can cancel a SQL statement in a session using the ALTER SYSTEM CANCEL SQL statement.

Instead of terminating a session, you can cancel a high-load SQL statement in a session. When you cancel a DML statement, the statement is rolled back.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

If @INST_ID is not specified, the instance ID of the current session is used.

If SQL_ID is not specified, the currently running SQL statement in the specified session is terminated.

Answer 5

Oh! just read comments in question, dear I missed it. but just letting the answer be here in case it can be useful to some other person

I tried "Ctrl+C" and "Ctrl+ Break" none worked. I was using SQL Plus that came with Oracle Client 10.2.0.1.0. SQL Plus is used by most as client for connecting with Oracle DB. I used the Cancel, option under File menu and it stopped the execution!

Once you click File wait for few mins then the select command halts and menu appears click on Cancel.

Answer 6

If you want to stop process you can kill it manually from task manager onother side if you want to stop running query in DBMS you can stop as given here for ms sqlserver T-SQL STOP or ABORT command in SQL Server Hope it helps you