On Fri, 4 Jun 1999, Carlos A. Costa wrote:
> We need to know what users and SQL statements are locking rows/tables on a
> database. Any idea about how we can create a script to do this?
> 
> Thanks.
> Carlos
> 
Carlos,

Oracle comes with some great scripts to make extra lock-related views. In
$ORACLE_HOME/rdbms/admin, there are two scripts for you:

1) catblock.sql : creates the following views:
   * DBA_LOCKS : all locks, and if they are blocking other users
   * DBA_LOCK_INTERNAL : all locks and requests for locks
   * DBA_DML_LOCKS : each DML (Data Manipulation) lock and request for
     lock
   * DBA_DDL_LOCKS : each DDL (Data Definition) lock and request for lock
   * DBA_WAITERS : shows the SID of each session waiting for a lock
   * DBA_BLOCKERS : shows the SID for each session holding a lock that
     another session is waiting to free up
2) utllockt.sql : generates a nice tree-structure format of all sessions
     that are holding locks and the users that are waiting for those locks
     to free up. The tree can have several levels.
     For utllockt.sql to work, you need to run catblock.sql first (this is
     NOT documented as far as I could see!)

If you want more information than the above, download my "Life Without
Tools: Monitoring Your Database With The Power Of SQL" PowerPoint presentation.
One of the tips addresses your question.

Hope that this helps!

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 300+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page tm"> Back to Ari Kaplan's Home Page