Re: How to get the SQL query of a blocking sid during a lock contention

There are several steps involved. First you find victim, then you find blocker, then you find out blocker sql hash_value, then you use it to find out the sql_text.

To find out lock victims:

select * from v$lock where request !=0

Then you can use id1 of the blocked session to find out the blocker:

select * from v$lock where id1 = &id1 and request = 0

Now you have the blocker. You can use the sid to find out its sql_hash_value,

select sql_hash_value from v$session where sid = &sid

Then use sql_hash_value to find out the sql text in v$sql::

select sql_Text from v$sql where hash_value = &sql_hash_value

You can probably put all this into one script. But this is easier to illustrate. Received on Mon Jul 11 2005 - 12:03:47 CDT