问题描述

如何通过终止会话来停止正在运行的 SELECT 语句?

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

该命令根据 SELECT 语句不断给我输出，我想在两者之间停止它.

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

推荐答案

当您不断获得结果页面时，我假设您在 SQL*Plus 中启动了会话.如果是这样，最简单的方法就是 bash ctrl + break 很多很多次，直到它停止.

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.

其中大部分都是不推荐的，可能会产生意想不到的后果.

根据 ObiWanKenobi 的回答 和 ALTER SESSION 文档

alter system kill session 'sid,serial#';

要查找 sid、会话 ID 和 serial#、序列号，请运行以下查询 - 总结自 OracleBase - 并找到您的会话:

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'

如果您正在运行 RAC 那么你需要稍微改变这个以考虑到多个实例，inst_id 是用来标识它们的:

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'

如果您没有运行 RAC，此查询也可以使用.

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

如果您使用的是 PL/SQL Developer 之类的工具，那么会话窗口也会帮助您找到它.

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

对于稍微强一点的kill"，您可以指定 IMMEDIATE 关键字，它指示数据库不要等待事务完成:

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.操作系统级别 - 发布 SIGTERM

kill pid

这假设您使用的是 Linux 或其他 *nix 变体.SIGTERM 是操作系统向特定进程发出的终止信号，要求它停止运行.它试图让进程优雅地终止.

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.

弄错了可能会导致您终止基本的操作系统进程，因此在输入时要小心.

您可以通过运行以下查询找到 pid、进程 ID，它还会告诉您有用的信息，例如运行进程的终端和运行它的用户名，以便您可以确保您选择正确的.

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# = ?

再说一次，如果您正在运行 RAC，则需要将其稍微更改为:

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# = ?

将 where 子句更改为 where s.status = 'KILLED' 将帮助您找到仍在运行"的已终止进程.

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

kill -9 pid

使用您在 2 中选择的相同 pid，SIGKILL 是从操作系统发送到特定进程的信号，该信号会导致进程立即终止.再次打字时要小心.

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.

这应该很少有必要.如果您正在使用 DML 或 DDL 它将停止正在处理的任何回滚，并且可能使得在发生故障时难以将数据库恢复到一致状态.强>

shutdown immediate

shutdown abort

reboot