如何对Oracle进行ORA-02049的故障排除和锁定问题 [英] How to troubleshoot ORA-02049 and lock problems in general with Oracle

问题描述

我偶尔会因一些长时间运行和/或密集的事务而收到ORA-02049.似乎没有任何模式，但是它发生在简单的INSERT上.

I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT.

我不知道如何获取任何信息或Oracle，但是必须有办法吗?是通过日志锁定还是至少可以查看当前锁定?

I have no clue how to get any sort of information out or Oracle, but there has to be a way? A log over locking or atleast a way to see current locks?

推荐答案

一种可能的方法是将distributed_lock_timeout的INIT.ORA参数增加到更大的值.这样一来，您将有更长的时间观察v$lock表，因为锁将持续更长的时间.

One possible way might be to increase the INIT.ORA parameter for distributed_lock_timeout to a larger value. This would then give you a longer time to observe the v$lock table as the locks would last for longer.

要实现此目的的自动化，您可以

To achieve automation of this, you can either

• 每5-10秒运行一次SQL作业，将v$lock或sandos上面给出的查询的值记录到表中，然后对其进行分析以查看哪个会话导致了锁定.

• Run an SQL job every 5-10 seconds that logs the values of v$lock or the query that sandos has given above into a table and then analyze it to see which session was causing the lock.

运行STATSPACK或AWR报告.被锁定的会话应该以较高的时间显示，因此可以被识别.

Run a STATSPACK or an AWR Report. The sessions that got locked should show up with high elapsed time and hence can be identified.

v$session还有3列blocking_instance, blocking_session, blocking_session_status列，可以将其添加到上面的查询中，以显示锁定的内容.

v$session has 3 more columns blocking_instance, blocking_session, blocking_session_status that can be added to the query above to give a picture of what is getting locked.

这篇关于如何对Oracle进行ORA-02049的故障排除和锁定问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！