Ask Me

Scripts for to Find Blocker Session

 





1. Find Blockers and waiters sessions on oracle 
****************************************************************

col BLOCKER format a9
col WAITER format a9
SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER
FROM v$lock
WHERE request > 0 OR block > 0 ORDER BY block DESC;
2. Find Session details information by passing session id
****************************************************************
col username     format a10
col osuser       format a10
col sid          format 9990
col serial#      format 999990
col status       format a10
col machine      format a40 truncated
col program      format a40 truncated
col terminal     format a30 truncated
col spid         format a9
col sql_text     format a3500 wrap
col logon_time   format a17
col last_call_et format a12

col NL  newline;

set long 1000
set heading off
set linesize 120
break on sid skip 1

select 'SID:  ' ||s.sid||
       '  SERIAL#: ' || s.serial# ||
       '  ORAUSER: ' || s.username ||
       '  OSUSER: ' || s.osuser ||
       '  STATUS: ' || s.status ||
       '  FRGRND: ' || s.process ||
       '  PID: ' || p.spid NL,
       'KILL: '   ||s.sid||','||s.serial#||
       '  LOGON: ' ||  decode(s.logon_time,'','NULL', to_char(s.logon_time,'MM/DD/YYYY HH12:MI:SS AM')) ||
       '  IDLE ( Day:Hr:Min:Sec ) : ' ||   decode(s.last_call_et,'','NULL',
-- days
substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
-- hours
 substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2))  NL,
       'MACHINE: '  || decode(s.MACHINE,'','NULL',s.MACHINE) ||
       '  TERMINAL: '  || decode(s.TERMINAL,'','NULL',s.TERMINAL) ||
       '  PROGRAM: '  || decode(s.PROGRAM,'','NULL',s.PROGRAM)  NL,
       'DISK READS: '  || decode(a.DISK_READS,'','NULL',a.DISK_READS) ||
       '  BUFFER GETS: ' || decode(a.BUFFER_GETS,'','NULL',a.BUFFER_GETS) ||
       '  PARSE CALLS: ' || decode(a.PARSE_CALLS,'','NULL',a.PARSE_CALLS) ||
       '  EXECUTIONS: '  || decode(a.EXECUTIONS,'','NULL',a.EXECUTIONS)  NL,
       'SQLTEXT: ' ||  decode(a.sql_text,'','NULL', a.sql_text) NL,
       'WAIT EVENT: '  || W.EVENT
from V$SESSION S, V$PROCESS P, V$SQLAREA A, V$SESSION_WAIT W
where paddr = addr
  and a.address(+) = s.sql_address
  and s.SID = w.SID(+)
  and   s.sid=&oracle_sid
/
 
********************************
*    Enter Process id get details             *
********************************
col username     format a10
col osuser       format a10
col sid          format 9990
col serial#      format 999990
col status       format a10 
col machine      format a40 truncated 
col program      format a40 truncated
col terminal     format a30 truncated
col spid         format a9
col sql_text     format a3500 wrap
col logon_time   format a17
col last_call_et format a12

col NL  newline;

set long 1000
set heading off
set linesize 120
break on sid skip 1

select 'SID:  ' ||s.sid||
       '  SERIAL#: ' || s.serial# ||
       '  ORAUSER: ' || s.username ||
       '  OSUSER: ' || s.osuser ||
       '  STATUS: ' || s.status ||
       '  FRGRND: ' || s.process ||
       '  PID: ' || p.spid NL, 
       'KILL: '   ||s.sid||','||s.serial#||
       '  LOGON: ' ||  decode(s.logon_time,'','NULL', to_char(s.logon_time,'MM/DD/YYYY HH12:MI:SS AM')) ||
       '  IDLE ( Day:Hr:Min:Sec ) : ' ||   decode(s.last_call_et,'','NULL',
-- days
substr('0'||trunc(last_call_et/86400),-2,2) || ':' || 
-- hours 
 substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' || 
-- minutes 
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' || 
--seconds 
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2))  NL,
       'MACHINE: '  || decode(s.MACHINE,'','NULL',s.MACHINE) || 
       '  TERMINAL: '  || decode(s.TERMINAL,'','NULL',s.TERMINAL) ||
       '  PROGRAM: '  || decode(s.PROGRAM,'','NULL',s.PROGRAM)  NL,
       'DISK READS: '  || decode(a.DISK_READS,'','NULL',a.DISK_READS) ||
       '  BUFFER GETS: ' || decode(a.BUFFER_GETS,'','NULL',a.BUFFER_GETS) ||
       '  PARSE CALLS: ' || decode(a.PARSE_CALLS,'','NULL',a.PARSE_CALLS) ||
       '  EXECUTIONS: '  || decode(a.EXECUTIONS,'','NULL',a.EXECUTIONS)  NL, 
       'SQLTEXT: ' ||  decode(a.sql_text,'','NULL', a.sql_text) NL, 
       'WAIT EVENT: '  || w.EVENT
from V$SESSION S, V$PROCESS P, V$SQLAREA A, V$SESSION_WAIT W
where paddr = addr
  and a.address(+) = s.sql_address
  and s.SID = w.SID(+)
  and   p.spid=&PID
/
********************************
* How to find lock on a specific table  *
********************************
1. Get the object ID of the locked table:

SELECT object_id FROM dba_objects WHERE object_name = '&TABLENAME' AND object_type='TABLE';
SELECT object_id,OBJECT_NAME,OWNER FROM dba_objects WHERE object_name = '&TABLENAME' AND object_type='TABLE' AND OWNER='&TABLE_OWNER';

SELECT object_id,OBJECT_NAME,OWNER FROM dba_objects WHERE object_name = UPPER('&TABLENAME') AND object_type='TABLE' AND OWNER=UPPER('&TABLE_OWNER');


2. Get the SID values for this ID:

SELECT sid FROM v$lock WHERE id1=1420294 FROM STEP1

 as sysdba

3. Get the session values for these SIDs:

SELECT sid, serial# from v$session where sid in (COMMA SEPARATED LIST OF SIDs FROM STEP2.)

4. Kill the sessions causing the lock:

ALTER SYSTEM KILL SESSION (SID,SERIAL#) pair values from step 3
e.g. ALTER SYSTEM KILL SESSION '231,23454'
 ********************************
 * Root Blockers                                       *
********************************
set linesize 200;
col machine for a22;
col os_user for a22;
col pid for a22;
col or_user for a22;
col sid for a11;
col serial for a11;
col block for a11;
col ctime for a22;
set pagesize 500;
select distinct 'WAITERS',
       substr(t.sid,1,4) SID,
       substr(S.SERIAL#,1,7) SERIAL,
       substr(t.CTIME,1,8) CTIME,
       substr(t.BLOCK,1,6) BLOCK,
       substr(s.PROCESS,1,12) PID,
       substr(s.machine,1,20) MACHINE,
       substr(s.OSUSER,1,8) OS_USER,
       substr(s.USERNAME,1,8) OR_USER
from V$TRANSACTION_ENQUEUE t,
     V$SESSION S,
     dba_waiters d
where S.SID = t.sid
and d.waiting_session = s.sid
union
select distinct 'HOLDERS',
       substr(t.sid,1,4) SID,
       substr(S.SERIAL#,1,7) SERIAL,
       substr(t.CTIME,1,8) CTIME,
       substr(t.BLOCK,1,6) BLOCK,
       substr(s.PROCESS,1,12) PID,
       substr(s.machine,1,20) MACHINE,
       substr(s.OSUSER,1,8) OS_USER,
       substr(s.USERNAME,1,8) OR_USER
from V$TRANSACTION_ENQUEUE t,
     V$SESSION S,
     dba_waiters d
where S.SID = t.sid
and d.holding_session = s.sid
and d.holding_session not in (
select waiting_session
from dba_waiters
)
union
select distinct 'USER_WITH_LOCK',
       substr(t.sid,1,4) SID,
       substr(S.SERIAL#,1,7) SERIAL,
       substr(t.CTIME,1,8) CTIME,
       substr(t.BLOCK,1,6) BLOCK,
substr(v.PROCESS,1,12) PID,
       substr(s.machine,1,20) MACHINE,
       substr(v.OS_USER_NAME,1,8) OS_USER,
       substr(v.ORACLE_USERNAME,1,8) OR_USER
from V$TRANSACTION_ENQUEUE t, v$locked_object v, sys.obj$ o, V$SESSION S
where t.sid = v.session_id
and o.obj# = v.object_id
and S.SID = t.sid;

 
********************************
Long Running Sessions                         *
********************************

select sid, serial#, context,
          round(sofar/DECODE(totalwork,0,sofar,totalwork)*100,2) "% Complete",
          substr(to_char(sysdate,'yymmdd hh24:mi:ss'),1,15) "Time Now",
          elapsed_seconds,message
from   v$session_longops where round(sofar/DECODE(totalwork,0,sofar,totalwork)*100,2) < 100
/


SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
col MESSAGE for a33
set lines 200
col opname for a11
col sid for a11
col serial# for a11
col target for a15
col totalwork for a11
col START_TIME for a22
col units for a11
col last_update_time for a22
col time_remaining for a20
SELECT SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;
****************************************************
* How to find Inactive session more than 30 min                        *
****************************************************
col username     format a10
col osuser       format a10
col sid          format 9990
col serial#      format 999990
col status       format a10
col machine      format a40 truncated
col program      format a40 truncated
col terminal     format a30 truncated
col spid         format a9
col sql_text     format a3500 wrap
col logon_time   format a17
col last_call_et format a12

col NL  newline;

set long 1000
set heading off
set linesize 120
break on sid skip 1

select 'SID:  ' ||s.sid||
       '  SERIAL#: ' || s.serial# ||
       '  ORAUSER: ' || s.username ||
       '  OSUSER: ' || s.osuser ||
       '  STATUS: ' || s.status ||
       '  FRGRND: ' || s.process ||
       '  PID: ' || p.spid NL,
       'KILL: '   ||s.sid||','||s.serial#||
       '  LOGON: ' ||  decode(s.logon_time,'','NULL', to_char(s.logon_time,'MM/DD/YYYY HH12:MI:SS AM')) ||
       '  IDLE ( Day:Hr:Min:Sec ) : ' ||   decode(s.last_call_et,'','NULL',
-- days
substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
-- hours
 substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2))  NL,
       'MACHINE: '  || decode(s.MACHINE,'','NULL',s.MACHINE) ||
       '  TERMINAL: '  || decode(s.TERMINAL,'','NULL',s.TERMINAL) ||
       '  PROGRAM: '  || decode(s.PROGRAM,'','NULL',s.PROGRAM)  NL,
       'DISK READS: '  || decode(a.DISK_READS,'','NULL',a.DISK_READS) ||
       '  BUFFER GETS: ' || decode(a.BUFFER_GETS,'','NULL',a.BUFFER_GETS) ||
       '  PARSE CALLS: ' || decode(a.PARSE_CALLS,'','NULL',a.PARSE_CALLS) ||
       '  EXECUTIONS: '  || decode(a.EXECUTIONS,'','NULL',a.EXECUTIONS)  NL,
       'SQLTEXT: ' ||  decode(a.sql_text,'','NULL', a.sql_text) NL,
       'WAIT EVENT: '  || W.EVENT
from V$SESSION S, V$PROCESS P, V$SQLAREA A, V$SESSION_WAIT W
where paddr = addr
  and a.address(+) = s.sql_address
  and s.SID = w.SID(+)
  and   s.last_call_et > 1800 and s.username is not null
/
SELECT l.sid,s.serial# FROM v$lock l,v$session s WHERE id1 in (SELECT object_id FROM dba_objects WHERE object_name = '&TABLENAME' AND object_type='TABLE' AND OWNER='&TABLE_OWNER') and l.sid=s.sid;


select 'alter system kill session ' || ''''|| sid || ',' || serial# || ''' immediate;' from V$session where sid in (SELECT sid FROM v$lock WHERE id1=73391);


SQL> select object_id,object_name,object_type from dba_objects where object_name like 'ghhj' and object_type='TABLE';


OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- --------------


SELECT /* TARGET SQL */ * FROM dual;


SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT /* TARGET SQL */%'


SQL_ID SQL_TEXT
------------- ---------------------------------------


Popular Posts