如何杀死正在运行的 SELECT 语句 [英] How to kill a running SELECT statement

问题描述

如何通过终止会话来停止正在运行的 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 它将停止正在处理的任何回滚，并且可能使得在发生故障时难以将数据库恢复到一致状态.强>

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.

所有剩余的选项将终止所有会话并导致您的数据库 - 在 6 和 7 服务器的情况下 - 变得不可用.仅在绝对必要时才应使用它们...

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 - 关闭数据库

4. Oracle - Shutdown the database

shutdown immediate

这实际上比 SIGKILL 更礼貌，尽管显然它作用于数据库中的所有进程而不是您的特定进程.对您的数据库保持礼貌总是很好.

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.

只有在您的 DBA 同意的情况下才能关闭数据库(如果您有 DBA).很高兴告诉使用数据库的人.

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.

shutdown abort

这与 SIGKILL 大致相同，但再次针对数据库中的所有进程.这是对数据库的一个信号，立即停止一切并死掉 - 一次严重的崩溃.它终止所有会话并且不回滚；因此，这可能意味着数据库需要更长时间才能再次startup.尽管使用了煽动性语言，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.

reboot

显然，这不仅会停止数据库，还会停止服务器，因此请谨慎使用，并征得您的系统管理员以及 DBA、开发人员、客户和用户的同意.

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.

我已经重启无效...一旦你达到这个阶段，你最好希望你使用的是虚拟机.我们最终删除了它...

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...

这篇关于如何杀死正在运行的 SELECT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！