Crontab : Blocking session
3 min readBlocking sessions in Oracle are sessions which holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data.
This will block the second session until the first session has completed its work.
crontab -e
*/30 * * * * /opt/app/oracle/admin/asrblg/scripts/session_lock_asrblg1.sh >/opt/app/oracle/admin/asrblg/scripts/asrblg1_lock_cron.log 2>&1
Grant 777 permission to session_lock_asrblg1.sh script for its execution.
[oracle@asrblg ~]$ chmod -R 777 session_lock_asrblg1.sh
[oracle@asrblg ~]$vi /opt/app/oracle/admin/asrblg/scripts/session_lock_asrblg.sh
#!/bin/bash
export FILE_PATH=/opt/app/oracle/admin/asrblg/scripts
cd $FILE_PATH
export ORACLE_SID=asrblg1
export ORACLE_HOME=/opt/app/oracle/product/12.2.0/db_1
$ORACLE_HOME/bin/sqlplus -S sys/oracle@123@asrblg as sysdba <<\EOF > deadlock_alert_asrblg1.log
SET FEEDBACK OFF;
SET PAGES 200 LINES 200;
COL SESS FOR 9999;
COL serial# FOR 9999;
COL type FOR a3;
COL USERNAME FOR a10;
COL SCHEMANAME FOR a100;
COL status FOR a100;
COL OSUSER FOR a100;
SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,
s.serial#,
s.inst_id,
a.SQL_FULLTEXT,
l.type,
s.USERNAME,
s.SCHEMANAME,
s.status,
s.OSUSER,
l.CTIME,
l.id1,
l.id2,
l.lmode,
l.request
FROM gV$LOCK l, gV$session s,
gv$sqlarea a
WHERE (l.id1, l.id2, l.type) IN
(SELECT l1.id1, l1.id2, type FROM gV$LOCK l1 WHERE request > 0)
and l.sid = s.sid
and l.type = 'TX'
and l.inst_id=s.inst_id
and s.inst_id=a.inst_id
and a.SQL_ID=s.SQL_ID
ORDER BY l.id1, l.request;
exit
EOF
cat deadlock_alert_asrblg1.log | grep -v "rows" > adeadlock_alert_asrblg1.log
sed '/-/d' deadlock_alert_asrblg1.log > final_deadlock_alert_asrblg1.log
awk '{if (++dup[$0] == 1) print $0;}' final_deadlock_alert_asrblg1.log > finaldeadlock_alert_asrblg1.log
MAIL_ALERT=`cat finaldeadlock_alert_asrblg1.log | wc -l`
if [ "$MAIL_ALERT" -gt "0" ]
then
##EMAIL_ID=`cat /opt/oracle/backup/scripts/mail_id_list`
EMAIL_ID=anish.kumar.roy@gmail.com
export EMAIL_ID
mailx -s "Lock Detected in asrblg1 `date +\%d\%m\%Y\%R`" $EMAIL_ID < $FILE_PATH/deadlock_alert_asrblg1.log
fi
rm *deadlock_alert_asrblg1.log
Find Locked Table
col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col owner head "Table|Owner" form a10
col mode_held form a15
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
/
Find Lock Wait Time
SELECT
blocking_session "BLOCKING_SESSION",
sid "BLOCKED_SESSION",
serial# "BLOCKED_SERIAL#",
seconds_in_wait/60 "WAIT_TIME(MINUTES)"
FROM v$session
WHERE blocking_session is not NULL
ORDER BY blocking_session;
Find Blocked Sessions
select a.SID "Blocking Session", b.SID "Blocked Session"
from v$lock a, v$lock b
where a.SID != b.SID and a.ID1 = b.ID1 and a.ID2 = b.ID2 and
b.request > 0 and a.block = 1;
col blocking_status for a 180;
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine
|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
Find Blocked SQL
SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT
FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC
WHERE
SES.SQL_ID=SQL.SQL_ID AND
SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND
SES.PADDR=PRC.ADDR AND
SES.SID=&Enter_blocked_session_SID;
