有没有的可以一下子找出所有的tx锁的锁定关系sql？
数据库版本10.2.0.4，我模拟出来一个这样的场景：select * from v$lock where sid <=155 and type='TX' order by id1,id2,lmode desc ,ctime desc;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
000007FF1DB281A8 000007FF1DB281E0 148 TX 65580 240 6 0 1594 1
000007FF1E8DB5A8 000007FF1E8DB5C8 149 TX 65580 240 0 6 1522 0
000007FF1DADE8C8 000007FF1DADE900 149 TX 196638 252 6 0 1609 1
000007FF1E8DBB00 000007FF1E8DBB20 144 TX 196638 252 0 6 671 0
000007FF1DB030E8 000007FF1DB03120 146 TX 655370 239 6 0 1621 1
000007FF1E8DB640 000007FF1E8DB660 148 TX 655370 239 0 6 1513 0
000007FF1E8DB6D8 000007FF1E8DB6F8 152 TX 655370 239 0 6 740 0
SELECT inst_id,
DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
id1,
id2,
lmode,
request,
type
FROM gV$lock
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$lock WHERE request > 0)
ORDER BY id1,id2,request;
INST_ID SESS ID1 ID2 LMODE REQUEST TYPE
1 Holder: 148 65580 240 6 0 TX
1 Waiter: 149 65580 240 0 6 TX
1 Holder: 149 196638 252 6 0 TX
1 Waiter: 144 196638 252 0 6 TX
1 Holder: 146 655370 239 6 0 TX
1 Waiter: 148 655370 239 0 6 TX
1 Waiter: 152 655370 239 0 6 TX
可以看到：
sid 148阻塞了149，149阻塞了144，而148受146的阻塞，同时146除了阻塞148以外，还阻塞152.
也就是说整个的enqueue chain或者等待关系是：
144=>149=>148=>146
152=>148=>146
也就是说此时146是源头。
我现在想的是有没有sql，可以得到一目了然的“等待关系”的结果，而不用我们再去做“二次识别”
？
我想是不是可能需要用到分析函数啊什么的 TRY:
@?/rdbms/admin/utllockt ok,thank you. 多谢，刘大，我执行了下，这个结果已经很理想了。谢谢。 我说的结果指的是：
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
146 None
148 Transaction Exclusive Exclusive 131081 246
149 Transaction Exclusive Exclusive 65540 242
144 Transaction Exclusive Exclusive 327702 243
表已删除。
SQL>
Hang/Locking > Determine a Cause >Overview
At this point you should have identified if the issue you are experiencing is a "True" database hang, "Stuck" or "Locked" sessions, OS resource
problem, or slow database. The "Determine a Cause" portion of the guide will assist you in troubleshooting "True" database hang, or "Stuck" or
"Locked" session related issues.
Data Collection
To further troubleshoot "Stuck" sessions, you will have several choices. You can troubleshoot by taking a closer look at the traces previously
generated (hanganalyze, systemstate, and errorstack dumps), by using OEM, by using utllockt.sql, or by performing queries on some V$ views.
Hanganalyze, systemstate, and errorstack dumps are great for collecting hang related data quickly, but understanding how to read these files can be
complex. It is not really possible to present all methods for interpreting these files, but examples will be provided in the Determine a Cause >
Analysis portion of the guide.
The hanganalyze, systemstate, and errorstack dumps are very useful (if collected as previously requested) when logging a service request because
they generally provide a good picture of what the database is doing when the sessions are hung.
Troubleshooting "Stuck" session issues by collecting data from the V$ views is less complex than reading the dump files. However, in order for this
to be effective, the sessions must remain in a "stuck" state until all the data is collected. In some cases this is not possible due to time constraints.
Analysis Approach
Further troubleshooting will:
1. Determine which wait events are involved
2. Determine the active SQL involved in the blocking and waiting sessions
3. Look for common causes for "Stuck" sessions
4. Review possible solutions for the likely cause
5. Implement the best solution
6. Verify that the solution solved the problem or if more work is needed 多谢上面的指点。
针对我的问题本身，针对@?/rdbms/admin/utllockt这个脚本在rac环境下，就不行了，我看了这个脚本的内容，暂时还不知道怎么去调整，因为两个节点上dba_lock里的内容不一样。。
页:
[1]