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#';
