Oracle：终止会话继续运行

Oracle: killed session continue running

提问by Revious

I can't simply stop it and it continues to read blocks and use rollback segments. It's a simple select but I fear it won't stop...

我不能简单地停止它，它会继续读取块并使用回滚段。这是一个简单的选择，但我担心它不会停止......

The session is marked as killed. What can I do?

会话被标记为已终止。我能做什么？

I've found some extra info on the following link: http://oracleunix.wordpress.com/2006/08/06/alter-system-kill-session-marked-for-killed-forever/but if I launche the following query it returns 241 records. What does it mean?

我在以下链接上找到了一些额外的信息：http: //oracleunix.wordpress.com/2006/08/06/alter-system-kill-session-marked-for-killed-forever/但如果我启动以下查询它返回 241 条记录。这是什么意思？

SELECT   spid
  FROM   v$process
 WHERE   NOT EXISTS (SELECT   1
                       FROM   v$session
                      WHERE   paddr = addr);

回答by Mark J. Bobak

If the session you kill had a large open transaction, it will have to roll back all those changes. So, you should see amount of undo being used go down, not up.

如果您杀死的会话有一个大的打开事务，它将必须回滚所有这些更改。因此，您应该看到正在使用的撤消量下降，而不是上升。

Try this query:

试试这个查询：

select vt.used_ublk from v$transaction vt, v$session vs where vs.taddr=vt.addr and vs.sid=&&sid;

Now, if you run the above query multiple times in succession, is used_ublk falling or increasing? If it's falling, then the session is rolling back.

现在，如果连续多次运行上述查询，used_ublk 是下降还是上升？如果它正在下降，则会话正在回滚。

Hope that helps.

希望有帮助。

回答by Ben

I'm going to assume that you session you killed was just a selectas you state and that you're operating on a *nix variant.

我将假设您杀死的会话就像select您所说的那样，并且您正在使用 *nix 变体。

If you're running an updateor deletethen waiting for the rollback to complete would be best. You can check the amount of rollback by using the following query, which I've shamelessly stolen from orafaqbecause I don't remember these things off the top of my head:

如果您正在运行update或delete等待回滚完成将是最好的。您可以使用以下查询来检查回滚量，这是我从orafaq无耻地窃取的，因为我不记得这些事情了：

select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets"
     , rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits"
     , rs.Shrinks "# Shrinks", rs.Extends "# Extends"
  from sys.v_$rollName rn, sys.v_$rollStat rs
 where rn.usn = rs.usn;

First off a selectshouldn't be using using rollback... if it does then you've probably got a function that does some DMLsomewhere, which isn't a very good idea. You also don't mention whether this selectis using a database link, if it is that clears things up a little bit.

首先，select不应该使用 using rollback ... 如果确实如此，那么您可能已经有一个在某处执行一些DML的函数，这不是一个好主意。你也没有提到这select是否使用了数据库链接，如果它可以稍微清理一下。

If the selectis not using a database link and is not doing any DML, then the link you've found will do everything you need. Your 241 rows, should mostly be identical - there may be more than one value if you have more than one process that has this problem. I would change the query to:

如果select没有使用数据库链接并且没有执行任何 DML，那么您找到的链接将完成您需要的一切。您的 241 行应该大部分相同 - 如果您有多个进程存在此问题，则可能会有多个值。我会将查询更改为：

select p.*
  from v$process p
  left outer join v$session s
    on p.addr = s.paddr
 where s.saddr is null

This means that you can check the username that owns the process the terminal it was run from and program that is running before doing anything drastic. You don't want to go around killing the wrong thing.

这意味着您可以在执行任何激烈操作之前检查拥有运行它的终端的进程和正在运行的程序的用户名。你不想四处寻找错误的东西。

You can then go direct to your box and issue the sigtermkill 1234. This issues a terminate signal to the process at the level of your OS and should get rid of it.

然后，您可以直接进入您的盒子并发出sigtermkill 1234。这会在您的操作系统级别向进程发出终止信号，应该摆脱它。

As an addendum, if your session is using a database link then killing it on the box it was running from is normally not enough. You may also have to kill it on the box that you're selecting from. Try the standard Oracle kill first and then scale it to OS level.

作为附录，如果您的会话正在使用数据库链接，那么在运行它的机器上杀死它通常是不够的。您可能还必须在您选择的框中将其杀死。首先尝试标准的 Oracle kill，然后将其扩展到操作系统级别。

This shouldwork. However, it's possible to get a lot more drastic; I've had to recently after a slave VM started accepting connections incoming and then not sending an error or returning a value.

这应该有效。然而，有可能变得更加激烈。我最近不得不在从虚拟机开始接受传入的连接然后不发送错误或返回值之后。

Warning: The more violent you get to the box the more violent it will be to you and the more likely things are to go wrong.

警告：你越靠近盒子，它对你就越猛烈，事情就越有可能出错。

The next step up from a sigtermis a sigkill. This is a signal to the OS to kill a process without asking any questions. On *nix this is kill -9 1234. This should rarely be necessary. If you were doing DML it will stop any rollback and may make it difficult to recover the database to a consistent state in the event of failure.

从下一步了SIGTERM是SIGKILL。这是向操作系统发出的信号，可以在不询问任何问题的情况下终止进程。在 *nix 上，这是kill -9 1234. 这应该很少是必要的。如果您正在执行 DML，它将停止任何回滚，并且可能难以在发生故障时将数据库恢复到一致状态。

If this still doesn't work then you have major problems. In the example given with the VM we ended up doing the following in order to stop the problem. Most of these are not recommended :-).

如果这仍然不起作用，那么您就有了重大问题。在 VM 给出的示例中，我们最终执行以下操作以解决问题。其中大部分不推荐:-)。

1. Oracle - alter system kill 123
2. OS - kill 1234
3. OS - kill -9 1234
4. Oracle - shutdown immediate- this is actually politer than kill -9 ..... It doesn't send a sigkillto the OS and waits for processes to rollback etc. Butit's always good to be polite to your database.
5. Oracle - shutdown abort- this is about the same as a sigkill. It's a signal to the database to stop everything immediately and die ( confusing terminology I know ).
6. OS - reboot
7. Yes that's right, rebootdidn't work. Once you've reached this stage you better hope you're using a VM. We ended up deleting it...

1. 甲骨文 - alter system kill 123
2. 操作系统 - kill 1234
3. 操作系统 - kill -9 1234
4. Oracle -shutdown immediate这实际上比kill -9 ..... 它不会向操作系统发送sigkill并等待进程回滚等。但是对您的数据库保持礼貌总是好的。
5. Oracle -shutdown abort这与sigkill 大致相同。这是向数据库发出立即停止一切并死亡的信号（我知道令人困惑的术语）。
6. 操作系统 - reboot
7. 是的，没错，reboot没用。一旦你达到这个阶段，你最好希望你使用的是虚拟机。我们最终删除了它......