Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to get the SQL query of a blocking sid during a lock contention

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

From: <bchorng_at_yahoo.com>
Date: 11 Jul 2005 10:03:47 -0700
Message-ID: <1121101427.413071.250600@f14g2000cwb.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US