如何杀死正在运行的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中启动了会话.如果是这样，最简单的方法就是对 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的答案和要查找sid，会话ID和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'

如果您正在运行 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.

要获得更强的杀伤力"，可以指定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.

输入错误可能会导致您终止必要的OS进程，因此输入时请务必小心.

通过运行以下查询，您可以找到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 或

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.

它将关闭数据库，终止所有会话，并对所有会话执行 rollback 未提交的交易.如果您有大型未提交的事务需要回滚，则可能需要一段时间.

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.

我无法重新启动...一旦达到此阶段，您最好希望您正在使用VM.我们最终删除了它...

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屋！