A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Crontab : Blocking session

3 min read
black and white photo of clocks

Photo by Andrey Grushnikov on Pexels.com

Blocking 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;