Oracle 用户断开连接而不提交/回滚，不再可以更改数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5932437/
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
Oracle user disconnected without committing/rolling back, not longer can change database
提问by mamontazeri
A user logged into the Oracle database I'm working with logged out without committing or rolling back and now my database cannot be changed, it seems to be locked.
登录到我正在使用的 Oracle 数据库的用户在未提交或回滚的情况下已注销，现在我的数据库无法更改，它似乎已被锁定。
I tried
我试过
rollback
but that didn't work.
但这没有用。
Any thoughts? Thanks.
有什么想法吗？谢谢。
回答by Justin Cave
It is impossible to log out of an Oracle database without committing or rolling back. It is possible that the client application crashed or was forcibly terminated and did not have a chance to log out leaving a session on the server that is holding some locks. If that is the case, the DBA would need to kill that session.
不提交或回滚就不可能注销 Oracle 数据库。客户端应用程序可能崩溃或被强行终止并且没有机会注销而在持有一些锁的服务器上留下会话。如果是这种情况，DBA 将需要终止该会话。
If you are the DBA and you're not sure what session needs to be killed, you can run the following query to get information about the various sessions that are holding locks that are blocking other sessions
如果您是 DBA 并且不确定需要终止哪个会话，则可以运行以下查询以获取有关持有阻塞其他会话的锁的各种会话的信息
SELECT ses.sid, ses.serial#, ses.username, ses.program, ses.osuser, ses.machine
FROM v$session ses,
dba_blockers blk
WHERE blk.holding_session = ses.sid
Once you've identified which session(s) to kill
一旦您确定了要终止的会话
ALTER SYSTEM KILL SESSION '<<sid>>, <<serial#>>'
回答by Gary Myers
If the session did not rollback explicitly, if the database server can no longer reach a client its activity will be rolled back automatically, and then it will terminate the session.
如果会话没有明确回滚，如果数据库服务器无法再访问客户端，其活动将自动回滚，然后将终止会话。
The server is often quite happy if the client doesn't make a request to it for hours on end. See if you have SQLNET.EXPIRE_TIME set to anything on the server. If it is a non-zero value, then that's how many minutes it will wait before checking to see if a client connection is dead. If it is zero, it won't check and will only be aware if it finds the client is dead when responding to a client request.
如果客户端连续几个小时没有向服务器发出请求，服务器通常会很高兴。查看您是否将 SQLNET.EXPIRE_TIME 设置为服务器上的任何内容。如果它是一个非零值，那么这就是在检查客户端连接是否已死之前它将等待的分钟数。如果它为零，它不会检查并且只会在响应客户端请求时发现客户端死机时才知道。
Also, the rollback may take some time. If a lot of work as been done, it may take hours. Anything waiting on that session will continue waiting until that rollback is complete
此外，回滚可能需要一些时间。如果完成了大量工作，则可能需要数小时。在该会话上等待的任何内容都将继续等待，直到回滚完成
