Comprehensive Guide to Terminating Running SELECT Statements in Oracle Database

Quick Termination in SQL*Plus Environment

When executing SELECT statements in SQL*Plus environment, if output results continue to display and immediate termination is required, the most direct method is to press the Ctrl + Break combination keys multiple times. This approach works in most cases and can quickly interrupt the current query execution.

Database-Level Session Termination

When keyboard shortcut methods are ineffective or precise control at the database level is needed, the ALTER SYSTEM command can be used to terminate specific sessions. First, obtain the target session's SID (Session ID) and SERIAL# (Serial Number):

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'

In RAC (Real Application Clusters) environments, the query needs slight modification to include instance ID:

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'

After obtaining SID and SERIAL#, execute the termination command:

alter system kill session 'sid,serial#';

If immediate termination without waiting for transaction completion is required, add the IMMEDIATE keyword:

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

Operating System-Level Process Management

In Linux or Unix-like systems, database processes can be terminated through operating system signals. First, obtain the Process ID (PID):

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

Use corresponding global views in RAC environments:

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

After obtaining the PID, send SIGTERM signal to request graceful process termination:

kill pid

If the process cannot terminate normally, use SIGKILL signal to force immediate termination:

kill -9 pid

It's important to note that SIGKILL immediately terminates the process and may affect database consistency recovery.

Database Shutdown Options

In extreme cases, shutting down the entire database might be necessary. Use the SHUTDOWN IMMEDIATE command:

shutdown immediate

This terminates all sessions and rolls back uncommitted transactions. For immediate shutdown, use SHUTDOWN ABORT:

shutdown abort

This method does not roll back transactions and may prolong database startup time.

System-Level Restart Options

As a last resort, the server can be rebooted:

reboot

This method affects all services running on the server and should be used cautiously.

Risks and Considerations

When terminating SELECT statements, the following risks should be considered: forced termination may affect database consistency, especially during DML or DDL operations. It's recommended to prioritize database-level termination methods and avoid directly manipulating operating system processes. Before implementing any termination operations, assess the impact on other system users.