Home > Uncategorized > Oracle documentation quirks

Oracle documentation quirks

When one tries to understand some concept in the Oracle database, it is often good to have a look into the documentation of previous releases. For example, a detailed explanation of data locks and concurrency can be found in documentation for Oracle 7.

I ran into it when searching for some explanation on how an Oracle query for detecting deadlocks works. A friend gave it to me in the following form:

SELECT s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status 
FROM v$lock l1, v$session s1, v$lock l2, v$session s2  
WHERE  s1.sid=l1.sid 
       AND s2.sid=l2.sid 
       AND l1.BLOCK=1 
AND  l2.request > 0 
AND l1.id1 = l2.id1 
AND l2.id2 = l2.id2; 

Some basic info about the meaning of ID1 and ID2 fields can be found here.

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: