SQL 如何杀死正在运行的 SELECT 语句

How to kill a running SELECT statement

提问by Ashish Anand

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.

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

回答by Ben

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+ breakmany, many times until it stops.

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

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

1、Oracle级别——杀死数据库中的进程

As per ObiWanKenobi's answerand the ALTER SESSIONdocumentation

根据ObiWanKenobi 的回答和ALTER SESSION文档

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:

要查找sid, 会话 ID 和serial#, 序列号，请运行以下查询 - 从OracleBase汇总- 并找到您的会话：

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 RACthen you need to change this slightly to take into account the multiple instances, inst_idis what identifies them:

如果您正在运行RAC，那么您需要稍微更改此设置以将多个实例考虑在内，inst_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'

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

如果您没有运行 RAC，此查询也将起作用。

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

如果您使用像 PL/SQL Developer 这样的工具，那么会话窗口也将帮助您找到它。

For a slightly stronger "kill" you can specify the IMMEDIATE keyword, which instructs the database to not wait for the transaction to complete:

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

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

2. OS level- Issue a SIGTERM

2.操作系统级别- 发出SIGTERM

kill pid

This assumes you're using Linux or another *nix variant. A SIGTERMis a terminate signal from the operating system to the specific process asking it to stop running. It tries to let the process terminate gracefully.

这假设您使用的是 Linux 或其他 *nix 变体。一个SIGTERM是从操作系统的具体过程要求它停止运行的终止信号。它试图让进程优雅地终止。

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.

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

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:

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

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 whereclause to where s.status = 'KILLED'will help you find already killed process that are still "running".

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

3. OS- Issue a SIGKILL

3.操作系统- 发出SIGKILL

kill -9 pid

Using the same pidyou picked up in 2, a SIGKILLis a signal from the operating system to a specific process that causes the process to terminate immediately. Once again be careful when typing.

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

This should rarely be necessary. If you were doing DMLor DDLit will stop any rollback being processed and maymake it difficult to recover the database to a consistent state in the event of failure.

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

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

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

4. Oracle- Shutdownthe database

4. Oracle-关闭数据库

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 alwaysgood to be polite to your database.

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

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.

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

It closes the database, terminating all sessions and does a rollbackon all uncommitted transactions. It can take a while if you have large uncommitted transactions that need to be rolled back.

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

5. Oracle- Shutdown the database ( the less nice way )

5. Oracle- 关闭数据库（不太好的方式）

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 startupagain. Despite the incendiary language a shutdown abortisn't pure evil and can normally be used safely.

这与SIGKILL大致相同，尽管再次在数据库中的所有进程上。这是给数据库的一个信号，立即停止一切并死掉 - 一次严重的崩溃。它终止所有会话并且不回滚；因此，这可能意味着数据库需要更长的时间才能startup再次运行。尽管有煽动性的语言，ashutdown abort并不是纯粹的邪恶，通常可以安全使用。

As before inform people the relevant people first.

和以前一样，先通知相关人员。

6. OS- Reboot the server

6.操作系统- 重启服务器

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.

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

7. OS- The last stage

7.操作系统——最后阶段

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

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

回答by James Drinkard

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 语句来终止这种格式的特定会话：

ALTER SYSTEM KILL SESSION 'sid,serial#'; 

For example:

例如：

ALTER SYSTEM KILL SESSION '314, 2643';

回答by Lukasz Szozda

There is no need to kill entire session. In Oracle 18c you could use ALTER SYSTEM CANCEL:

没有必要杀死整个会话。在 Oracle 18c 中，您可以使用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.

取消会话中的 SQL 语句

您可以使用 ALTER SYSTEM CANCEL SQL 语句取消会话中的 SQL 语句。

您可以取消会话中的高负载 SQL 语句，而不是终止会话。当您取消 DML 语句时，该语句将回滚。

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

如果未指定@INST_ID，则使用当前会话的实例 ID。

如果未指定 SQL_ID，则终止指定会话中当前运行的 SQL 语句。

回答by ObiWanKenobi

To kill a session in Oracle, you use the ALTER SYSTEM KILL SESSIONcommand.

要终止 Oracle 中的会话，请使用ALTER SYSTEM KILL SESSION命令。

Details here: http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

详情请见：http: //www.oracle-base.com/articles/misc/KillingOracleSessions.php

回答by Shiva

Oh! just read comments in question, dear I missed it. but just letting the answer be here in case it can be useful to some other person

哦！只是阅读有问题的评论，亲爱的，我错过了。但只是让答案在这里，以防它对其他人有用

I tried "Ctrl+C" and "Ctrl+ Break" none worked. I was using SQL Plus that came with Oracle Client 10.2.0.1.0. SQL Plus is used by most as client for connecting with Oracle DB. I used the Cancel, option under File menu and it stopped the execution!

我试过“Ctrl+C”和“Ctrl+Break”都没有用。我使用的是 Oracle Client 10.2.0.1.0 附带的 SQL Plus。SQL Plus 被大多数人用作连接 Oracle DB 的客户端。我使用了“文件”菜单下的“取消”选项，它停止了执行！

Once you click File wait for few mins then the select command halts and menu appears click on Cancel.

单击“文件”后，等待几分钟，然后选择命令停止并出现菜单，单击“取消”。

回答by Mulesoft Developer

If you want to stop process you can kill it manually from task manager onother side if you want to stop running query in DBMS you can stop as given here for ms sqlserver T-SQL STOP or ABORT command in SQL ServerHope it helps you

如果您想停止进程，您可以从另一端的任务管理器中手动终止它，如果您想停止在 DBMS 中运行查询，您可以按照此处给出的 ms sqlserver T-SQL STOP 或 SQL Server 中的 ABORT 命令停止希望它对您有所帮助