Friday, May 18, 2007

Oracle: Session Blocking

From time to time I get a call from the user community at large of people not being able to do anything cause the app is hung up.

It's always been due to a session blocking everyone else. Too bad the application doesn't handle this better on it's own.

Here's a query I use from time to time to see who is blocking.

Select /*+ RULE */ username, v$session.SID, serial#, owner, object_name, object_type, 
v$lock.TYPE
FROM dba_objects, v$lock, v$session
WHERE object_id = v$lock.id1 AND v$lock.SID = v$session.SID
AND owner != 'SYS'
AND block = 1

No comments: