Database lock checks

Introduction

If one wants to get to immediately know the Oracle database sessions that are blocking other sessions, here is one easy method.  There are various procedures to find Oracle database blocking (offending) sessions as well as waiting sessions.  This post describes one such procedure.

DBA_BLOCKERS and DBA_WAITERS

The first task an Oracle DBA has to do after executing a CREATE DATABASE statement (manual database creation method) is to run the $ORACLE_HOME/rdbms/admin/catalog.sql and $ORACLE_HOME/rdbms/admin/catproc.sql which help in creating the data dictionary objects.  However,  a few data dictionary objects have to be created separately by running other scripts.  Two of the important database locking views are viz. dba_blockers and dba_waiters respectively. These are created by running catblock.sql script. One can execute this as part of the initial database creation process or later as well. Executing this script does not cause any database downtime and hence can be run at any time.

1. sqlplus '/ as sysdba'
2. @?/rdbms/admin/catblock.sql

The DBA_BLOCKERS just gives the blocking session information. But DBA_WAITERS gives details about both blocking session and waiting session

v$session.blocking_session column

The v$session view also has a column named blocking_session.  If a database session is struck/waiting for processing, one can easily find its blocking session (session that is causing the wait)  by querying blocking_session column and then take suitable action. Furthermore, this column can help in situations when the relevant information is not present in DBA_BLOCKERS and DBA_WAITERS.