WAITER AND BLOCKER

Login to the database as SYS as SYSDBA and run the script which is create lock tables. $ORACLE_HOME/rdbms/admin/catblock.sql

SQL > @$ORACLE_HOME/rdbms/admin/catblock.sql

This will create following views.

dba_locks
dba_kgllock
dba_lock_internal
dba_dml_locks
dba_ddl_locks
dba_waiters
dba_blockers


---DBA_WAITERS---
Its shows which session is waiting for resources. In an Oracle RAC environment, this only applies if the waiter is on the same instance.

COLUMN: WAITING_SESSION
COLUMN: HOLDING_SESSION


---DBA_BLOCKERS---
Its shows which session is blocking to another session and resources.  In an Oracle RAC environment, this only applies if the blocker is on the same instance.


COLUMN : HOLDING_SESSION

@@Below the Demonstration.

                Open 3 sesison

                session 1: login as scott

                session 2: login as scott

                session 3: login as sys

                session 1: update emp set sal=sal*100 where deptno =20;   # No commit Perform

                session 2: update emp set sal=200;   # No commit Perform

                session 3:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters;

--select * from dba_blockers;

                session 1: rollback; else killed the session by sys.

                session 2:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters; # lock has been released

## Detect the object_name which is holding the lock by session.

                Open 3 sesison

                session 1: login as scott

                session 2: login as scott

                session 3: login as sys

                session 1: update emp set sal=sal*100 where deptno =20;   # No commit Perform

                session 2: update emp set sal=200;   # No commit Perform

                session 3:  select waiting_session,holding_session,lock_type,mode_held from dba_waiters;

               

                session 3: select user#,sid,row_wait_obj#,username,wait_class,blocking_session from v$session where blocking_session=<HOLDING_SESSION>;

                session 3: select owner,object_id,object_name from dba_objects where object_id=<ROW_WAIT_OBJ#>;

                session 3: select serial# from v$session where sid=<WAITING_SESSION>;

                session 3: alter system kill session 'sid,serial#';