Blocked Sessions in Oracle
Published by : Obay Salah , December 10, 2024
1- Get current session id.
SELECT SID, SERIAL# FROM V$SESSION WHERE SID = Sys_Context('USERENV', 'SID');
2- Create Below Table and Insert a Row Without Commit or Rollback.
create table lock_test( id# number primary key, value varchar2(20) );
3- Insert into table without commit or rollback.
3- insert into lock_test values (1, 'Insert lock test');
4- Open new session and insert same record in the same table.
3- insert into lock_test values (1, 'Insert lock test');
5- Find Blocked Sessions.
SELECT SESLCK.USERNAME|| '@'|| SESLCK.MACHINE|| '@INSTANCE'|| SESLCK.INST_ID|| ' (SID='|| SESLCK.SID|| ' SERIAL='|| SESLCK.SERIAL# || ' STATUS='|| SESLCK.STATUS|| ') IS BLOCKING '|| SEWT.USERNAME|| '@'|| SEWT.MACHINE|| '@INSTANCE'|| SEWT.INST_ID|| ' (SID=' || SEWT.SID|| ' SERIAL='|| SEWT.SERIAL#|| ' STATUS='|| SEWT.STATUS|| ' SQLID='|| SEWT.SQL_ID|| ')'LOCK_INFORMATION FROM GV$LOCK WT, GV$LOCK LCKR, GV$SESSION SESLCK, GV$SESSION SEWT WHERE LCKR.ID1 = WT.ID1 AND LCKR.SID = SESLCK.SID AND LCKR.INST_ID = SESLCK.INST_ID AND WT.SID = SEWT.SID AND WT.INST_ID = SEWT.INST_ID AND LCKR.ID2 = WT.ID2 AND LCKR.REQUEST = 0 AND WT.LMODE = 0;
6- Find Lock Wait Time
SELECT BLOCKING_SESSION "BLOCKING_SESSION", SID "BLOCKED_SESSION", SECONDS_IN_WAIT / 60 "WAIT_TIME(MINUTES)" FROM GV$SESSION WHERE BLOCKING_SESSION IS NOT NULL ORDER BY BLOCKING_SESSION;
7- Find Blocking SQL
SELECT * FROM GV$OPEN_CURSOR OC WHERE OC.INST_ID = :TYPE_BLOCKING_INSTANCE_ID AND OC.SID = :TYPE_BLOCKING_SID AND ( OC.SQL_TEXT LIKE 'INSERT%' OR OC.SQL_TEXT LIKE 'UPDATE%' OR OC.SQL_TEXT LIKE 'DELETE%') AND OC.CURSOR_TYPE = 'OPEN' ORDER BY OC.LAST_SQL_ACTIVE_TIME;
8- Find Blocked SQL
SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT FROM GV$SESSION SES, GV$SQL SQL, GV$PROCESS PRC WHERE SES.SQL_ID = SQL.SQL_ID AND SES.SQL_HASH_VALUE = SQL.HASH_VALUE AND SES.PADDR = PRC.ADDR AND SES.INST_ID = SQL.INST_ID AND SES.SID = &ENTER_BLOCKED_SESSION_SID;
9- Find Locked Table
SELECT LO.SESSION_ID, LO.ORACLE_USERNAME, LO.OS_USER_NAME, LO.PROCESS, DO.OBJECT_NAME, DO.OWNER, DECODE (LO.LOCKED_MODE, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE (SS)', 3, 'ROW EXCL (SX)', 4, 'SHARE', 5, 'SHARE ROW EXCL (SSX)', 6, 'EXCLUSIVE', TO_CHAR (LO.LOCKED_MODE)) MODE_HELD FROM GV$LOCKED_OBJECT LO, DBA_OBJECTS DO WHERE LO.OBJECT_ID = DO.OBJECT_ID ORDER BY 5;
10- kill blocking session inside Oracle.
SELECT 'ALTER SYSTEM KILL SESSION '''|| SESLCK.SID|| ','|| SESLCK.SERIAL#|| ',@'|| SESLCK.INST_ID|| ''' IMMEDIATE;' BLOCKING_SESSION_KILL_COMMAND FROM GV$LOCK WT, GV$LOCK LCKR, GV$SESSION SESLCK, GV$SESSION SEWT WHERE LCKR.ID1 = WT.ID1 AND LCKR.SID = SESLCK.SID AND LCKR.INST_ID = SESLCK.INST_ID AND WT.SID = SEWT.SID AND WT.INST_ID = SEWT.INST_ID AND LCKR.ID2 = WT.ID2 AND LCKR.REQUEST = 0 AND WT.LMODE = 0 AND WT.type='TX';
RESOLVING LOCKS IN ORACLE
As per Oracle, the blocked (or waiting) session will continue to wait until
- Blocking session issues a COMMIT
- Blocking session issues a ROLLBACK
- Blocking session disconnects from the database
Comments
no comment yet!