Re: Efficient way to detect lock contention/blocking sessions

To quickly ID what's going on in terms of locks/blocks I use the following script (tested/optimized on 8i/9i):

   http://www.dbatoolz.com/sql/s_locked_obj.sql

   Reports the following:

• object locks from V$LOCKED_OBJECT using PL/SQL loops since join of DBA_OBJECTS and V$LOCKED_OBJECT is extremly slow
• blocked objects from V$LOCK and SYS.OBJ$
• blocked sessions from V$LOCK
• pulls blokers session details from V$SESSION and V$SQLTEXT
  • Vitaliy -- http://www.freelists.org/webpage/oracle-l