How to kill a running SELECT statement
Solution 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...
Solution 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';
Solution 3:
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.
Solution 4:
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