如何杀死正在运行的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.

命令是根据SELECT语句不断地给我输出，我想在中间停止它。

6 个解决方案

#1

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.

当您不断获取结果页面时，我假设您在SQL*Plus中启动了会话。如果是这样，那么最简单的方法就是使用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.

更复杂和更一般的方式(s)我详述如下，以增加凶猛/邪恶。第一个可能对你有用，但如果你不能继续下去的话。

Most of these are not recommended and can have unintended consequences.

这些建议中的大多数都不被推荐，而且可能会产生意想不到的后果。

1. Oracle level - Kill the process in the database

As per ObiWanKenobi's answer and the ALTER SESSION documentation

根据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和序列号(序列号)，运行以下查询——从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 RAC then you need to change this slightly to take into account the multiple instances, inst_id is 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”，您可以指定即时关键字，它指示数据库不要等待事务完成:

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

2. OS level - Issue a SIGTERM

kill pid

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.

这假设您使用的是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 where clause to where s.status = 'KILLED' will help you find already killed process that are still "running".

将where子句改为where s。status = ' kill '将帮助你发现仍在运行的进程已经被杀死。

3. OS - Issue a SIGKILL

kill -9 pid

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.

使用您在2中获得的相同的pid, SIGKILL是一个从操作系统到一个特定进程的信号，它会导致进程立即终止。打字的时候还是要小心。

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.

这应该很少有必要。如果您正在执行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 - Shutdown the database

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 always good 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的同意，如果您有的话。告诉使用数据库的人也很好。

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.

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

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

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.

这几乎与SIGKILL相同，不过在数据库中的所有进程中都是如此。这是一个向数据库发出信号的信号，它可以立即停止一切，并造成严重的崩溃。它终止所有会话，并没有回滚;因此，这意味着数据库需要更长的时间才能启动。尽管有煽动性的语言，关闭中止并不是纯粹的邪恶，通常可以安全地使用。

As before inform people the relevant people first.

和以前一样，先把有关的人告诉别人。

6. OS - Reboot the server

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

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

我已经重启了，没有工作…一旦你达到这个阶段，你最好希望你使用的是虚拟机。我们最后删除了它…

#2

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';

#3

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

要在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

#4

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

在会话中取消SQL语句。

You can cancel a SQL statement in a session using the ALTER SYSTEM CANCEL SQL statement.

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

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.

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

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.

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

If SQL_ID is not specified, the currently running SQL statement in the specified session is terminated.

如果没有指定SQL_ID，那么指定会话中的当前运行的SQL语句将被终止。

#5

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客户端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.

一旦你点击文件，等待几分钟，然后选择命令停止和菜单出现点击取消。

#6

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 Server Hope it helps you

如果你想停止进程，你可以从另一边的任务管理器手动杀死它，如果你想停止在DBMS中运行查询，你可以在这里停止，因为在SQL Server中，sqlserver T-SQL stop或ABORT命令可以帮助你。