如何使用 Oracle 清理死连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/297840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I clean up dead connections using Oracle?
提问by Robert Gould
Right now I have a few new applications being developed against an Oracle Database, and sometimes they crash or fail to end correctly, etc... anyways the problem is they sometimes seem to leave their connections open, and I need to cleanup after them. My question is if there is a way from the database-side of things to determine dead connections and clean them up?
现在我有一些针对 Oracle 数据库开发的新应用程序，有时它们会崩溃或无法正确结束，等等......无论如何，问题是它们有时似乎使它们的连接保持打开状态，我需要在它们之后进行清理。我的问题是，是否有办法从数据库端确定死连接并清理它们？
采纳答案by Dana the Sane
Here's a page referring to connection timeout parameters you can set in Oracle 11g. I think the 'Abandon Connection Timeout' is what you're looking for.
这是一个页面，参考了您可以在Oracle 11g 中设置的连接超时参数。我认为“放弃连接超时”是您正在寻找的。
回答by WW.
You may also be interested in killing them. Running this script in SQL*Plus will give you a list of "kill" statements. You can pick out the ones you want to kill based on the sid and run those. Oracle has some of it's own internal connections, do not kill them.
您可能也有兴趣杀死它们。在 SQL*Plus 中运行此脚本将为您提供“kill”语句列表。您可以根据 sid 选择要杀死的那些并运行它们。Oracle 有一些它自己的内部连接，不要杀死它们。
SELECT 'alter system kill session ''' || sid || ',' || serial# || '''; ' || sql_id death
FROM v$session
/
回答by Justin Cave
I believe you are looking for the SQLNet.ora parameter EXPIRE_TIMEwhich tells the database to send a probe to the client every few minutes to verify that the connection is still alive.
我相信您正在寻找 SQLNet.ora 参数EXPIRE_TIME，它告诉数据库每隔几分钟向客户端发送一次探测，以验证连接是否仍然有效。
回答by Andrew Spencer
Here's how to identify the session to kill (you will need SID and SERIAL# to kill it). Should I mention that you need to make sure you're killing the right session? sys_context('userenv','sid')gets the SID of your own session.
以下是识别要终止的会话的方法（您将需要 SID 和 SERIAL# 来终止它）。我应该提到你需要确保你杀死了正确的会话吗？sys_context('userenv','sid')获取您自己会话的 SID。
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.osuser,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
You can then issue alter system kill session '[sid],[serial#]'as suggested by WW.
然后您可以alter system kill session '[sid],[serial#]'按照 WW 的建议发出。
However the alter system kill sessioncommand does not forcibly kill the session, rather it asks the session to die. If the session is really hung, you will find that the request hangs for 60 seconds and then returns ORA-00031 Session marked for kill. And the session is still there.
但是，该alter system kill session命令不会强制终止会话，而是要求会话终止。如果 session 真的挂了，你会发现请求挂了 60 秒然后返回ORA-00031 Session marked for kill。会话仍然存在。
In that case, first check that the session isn't rolling back a large transaction (cross reference the SID and SERIAL# from the above):
在这种情况下，首先检查会话是否没有回滚大事务（交叉引用上面的 SID 和 SERIAL#）：
SELECT s.username,
s.osuser,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
If a transaction is rolling back you will see USED_UREC decreasing. Leave it to complete rollback.
如果事务正在回滚，您将看到 USED_UREC 减少。让它完成回滚。
Otherwise, ALTER SYSTEM DISCONNECT SESSION '[sid],[serial#]' IMMEDIATE;will forcibly disconnect the session and roll back the open transaction.
否则，ALTER SYSTEM DISCONNECT SESSION '[sid],[serial#]' IMMEDIATE;将强行断开会话并回滚打开的事务。
All the above info came from here.
以上所有信息都来自这里。
