Crontab : Blocking session

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

[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

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
/

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;

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 ;

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;