网络中断导致跨 DB Link 的存储过程查询永远挂起
我支持通过 WAN 查询远程数据库的许多存储过程。 网络偶尔会出现故障，但最糟糕的情况是程序失败，必须重新启动。
过去几周事情发生了险恶的转变。 过程不会失败，而是挂在奇怪的锁定状态。 它们无法在 Oracle 内部被杀死，并且只要它们存在，任何运行该过程的其他副本的尝试也将挂起。 我们找到的唯一解决方案是使用操作系统中的“kill -9”来终止有问题的程序。 其中一些过程几个月甚至几年都没有更改，因此我怀疑根本原因在于数据库或数据库配置。
有人知道我们可以采取什么措施来解决这个问题吗？ 或者 PL/SQL 是否有一个超时机制，我可以将其添加到代码中，以便我可以创建一个可以通过编程方式处理的异常？
A number of stored procedures I support query remote databases over a WAN. The network occasionally goes down, but the worst that ever happened was the procedures failed and would have to be restarted.
The last couple weeks it's taken a sinister turn. Instead of failing the procedures hang in a wierd locked state. They can't be killed inside of Oracle and as long as they exist any attempt to run other copies of the procedure will hang too. The only solution we've found is to kill the offending procedures with a "kill -9" from the OS. Some of these procedures haven't been changed for months, even years, so I suspect a root cause in the DB or DB configuration.
Any one have any ideas of what we can do to either fix the problem? Or does PL/SQL have a time-out mechanism I can add to the code so that I can create an exception that I can handle programatically?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
什么数据库版本？ 他们是否被困在运行 SQL 或 PL/SQL 中？
最近有没有人在例程中添加异常处理？
我记得在 9iR2 中，我们被告知，我们要捕获所有异常并继续运行，而不是向调用例程引发异常（基本上尝试运行处理作业中的所有项目，即使某些项目失败）。
我们不可避免地会让作业陷入无限循环，SQL 失败，被异常处理程序捕获并重试。 而且它们无法被杀死，因为 WHEN OTHERS 也捕获了“您的会话已被杀死”异常。 我认为后者在 10g 中发生了变化，这样异常就不会被捕获。
What database version ? Are they stuck running SQL or in PL/SQL ?
Has anyone added exception handling into the routines recently ?
I remember in 9iR2, we were told that, instead or raising an exception to the calling routine, we were to catch all exceptions and keep running (basically try to run process all the items in the job even if some fail).
We inevitably had jobs get stuck in an infinite loop with SQLs failing, getting caught by the exception handler and trying again. And they couldn't be killed as the WHEN OTHERS also caught the 'your session has been killed' exception. I think the latter changed in 10g so that exception didn't get caught.
我们始终无法确定是什么导致了这种情况的发生。 我们认为这是 2008 年 10 月累积补丁中的一个缺陷。 也许后来的补丁修复了它。 这种情况已经几个月没有发生了（而且我们已经发生了一些网络中断），所以希望问题已经消失。
We were never able to determine what caused this to happen. We believe it was a defect in the October 2008 cumulative patch. Perhaps a later patch as fixed it. It hasn't happened for a couple months (and we've had some network outages) so hopefully the problem has gone away.