Sunday, 20 May 2012

Oracle Lock info

Oracle Lock info and oracle wait due to locks

Oracle deadlock
script to find the blocking sqls during the deadlock situations

---------------------------------------------------------------

Lock Mode (LMODE) higher in Number , more strong lock is
6 - Exclusive (X)
5 - Shared Row Exclusive (SRX)
4 - Share (S)
3 - Row Exclusive (RX)
2 - Row Share (RS)
1 - null (NULL)
0 - none


6 - Exclusive (X) The most restrictive locking mode; permits queries on the locked table but prohibits any DML by any other users. 

3 - Row Exclusive (RX) A row exclusive table lock generally indicates that the

 transaction holding the lock has made one or more 
    updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of 
    statements.

i) Row Level Locks (TX) Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row,
   A row lock is acquired.Any INSERT, DELETE, UPDATE, or SELECT FOR UPDATE statements will automatically issue an exclusive lock on the rows affected by the transaction. This exclusive lock at row means that other transactions can't modify theaffected rows until the original transaction commits or rolls back

ii) Table Level Lock (TM) Whenever you acquire any row level lock there is ultimately a table level is also acquired to prvent others session to alter, drop 
    this table whose rows are being modified.TM Per table locks are acquired during the execution of a transaction when referencing a 
    table with a DML statement so that the object is not dropped or altered during the execution of the transaction.


If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.The following queries shows you all the TM locks:



Wait Info due to lock ...


select WAIT_EVENT,lpad(TO_CHAR(PCTTOT,'990D99'),10)||'% waits with average duration ='||TO_CHAR(AVERAGE_WAIT_MS,'99999999990D99')||'ms' as EVENT_VALUES
from (
select --RANK() OVER (order by sum(time_waited) desc)  as RANK,
event as WAIT_EVENT--, sum(time_waited) time_waited_ms
,round(RATIO_TO_REPORT(sum(time_waited)) OVER ()*100,2) AS PCTTOT
,round(avg(average_wait)*10,2) as AVERAGE_WAIT_MS
from
(select se.SID, se.INST_ID, se.EVENT, se.TIME_WAITED,
se.AVERAGE_WAIT from gv$session_event se
where se.WAIT_CLASS not in ('Idle')
union
select ss.SID, ss.INST_ID, sn.NAME as EVENT
, ss.VALUE as TIME_WAITED
, 0 as AVERAGE_WAIT from gv$sesstat ss, v$statname sn
where ss."STATISTIC#" = sn."STATISTIC#"
and sn.NAME in ('CPU used when call started'))
where (sid, inst_id) in
(select sid, inst_id from gv$session
where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
group by event
order by PCTTOT desc) we;



WAIT_EVENT                                                       EVENT_VALUES
---------------------------------------------------------------- ----------------------------------------------------------
enq: TX - row lock contention                                         99.78% waits with average duration =     7844676.60ms
CPU used when call started                                             0.21% waits with average duration =           0.00ms
SQL*Net break/reset to client                                          0.00% waits with average duration =           1.55ms
SQL*Net more data to client                                            0.00% waits with average duration =           1.20ms
SQL*Net message to client                                              0.00% waits with average duration =           0.00ms
events in waitclass Other                                              0.00% waits with average duration =           0.00ms
db file scattered read                                                 0.00% waits with average duration =           9.10ms
direct path sync                                                       0.00% waits with average duration =           4.30ms
Disk file operations I/O                                               0.00% waits with average duration =           0.27ms
direct path write temp                                                 0.00% waits with average duration =           1.50ms
log file sync                                                          0.00% waits with average duration =          26.10ms
db file sequential read                                                0.00% waits with average duration =           2.90ms

12 rows selected.





If wait " Avg global enqueue get time (ms) " : is high then check the TX lock . This waits are due to lock

select s.sid, s.serial#, s.username, s.module, s.ROW_WAIT_OBJ# object_id,dbms_rowid.rowid_create(1, s.row_wait_obj#, s.row_wait_file#,
s.row_wait_block#, s.row_wait_row#) my_rowid,s.sql_hash_value, s.sql_address, sq.SQL_TEXT
from v$session_wait sw, v$session s, v$sql sq, v$lock l
where 1=1
and sw.event = 'enqueue'
and sw.sid = s.sid
and l.type = 'TX' 
and l.request = 6
and l.sid = s.sid
and s.sql_hash_value = sq.hash_value 
and s.sql_address = sq.address

set linesize 300  pagesize 60
column username 	for a21
column osuser 		for a8
column sid 			for 9,999,999
column serial# 		for 9,999,999
column lockwait 	for a20
column status 		for a8
column module 		for a20
column machine 		for a20
column program 		for a20
column logon_time 	for a20
col event 			for a20
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
substr(s.module,1,20) module,
substr(s.machine,1,20) machine,
substr(s.program,1,20) program,
substr(s.event,1,20) event,
sql_id,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   gv$session s
where s.serial# >10
and username is not null 
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
/

>12c
set linesize 300
select l1.sid,l1.con_id,l1.type ,l1.ctime, ' Is BLOCKING -->>> ' Blocking, l2.sid,l2.con_id,l2.type,l2.ctime  from gv$lock l1, gv$lock l2
where l1.block =1 
and  l2.request > 0 
and  l1.id1=l2.id1  
and  l1.id2=l2.id2
and l1.inst_id=l2.inst_id;
	
	




col BLOCKING_STATUS FORMAT A90
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 ;


BLOCKING_STATUS
------------------------------------------------------------------------------------------
ANUJ@anuj.kumarsingh.co.uk ( SID=17 )  is blocking ANUJ@anuj.kumarsingh.co.uk ( SID=34 )



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 ;

BLOCKING_STATUS
------------------------------------------------------------------------------------------
ANUJ@anuj.kumarsingh.co.uk ( SID=17 )  is blocking ANUJ@anuj.kumarsingh.co.uk ( SID=34 )




set linesize 110
col BLOCKING_STATUS format a110
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;


BLOCKING_STATUS
--------------------------------------------------------------------------------------------------------------
ANUJ@anuj.kumarsingh.co.uk ( INST=1 SID=17 ) IS BLOCKING ANUJ@anuj.kumarsingh.co.uk ( INST=1 SID=34 )

1 row selected.









SELECT lh.inst_id Locking_Inst,
       lh.sid     Locking_Sid,
       lw.inst_id Waiting_Inst,
       lw.sid     Waiter_Sid,
       decode (lh.type,    'MR', 'Media_recovery',
                           'RT', 'Redo_thread',
                           'UN', 'User_name',
                           'TX', 'Transaction',
                           'TM', 'Dml',
                           'UL', 'PLSQL User_lock',
                           'DX', 'Distrted_Transaxion',
                           'CF', 'Control_file',
                           'IS', 'Instance_state',
                           'FS', 'File_set',
                           'IR', 'Instance_recovery',
                           'ST', 'Diskspace Transaction',
                           'IV', 'Libcache_invalidation',
                           'LS', 'LogStaartORswitch',
                           'RW', 'Row_wait',
                           'SQ', 'Sequence_no',
                           'TE', 'Extend_table',
                           'TT', 'Temp_table',
                                 'Nothing-') Waiter_Lock_Type,
       decode (lw.request,  0,   'None',
                            1,   'NoLock',
                            2,   'Row-Share',
                            3,   'Row-Exclusive',
                            4,   'Share-Table',
                            5,   'Share-Row-Exclusive',
                            6,   'Exclusive',
                                 'Nothing-') Waiter_Mode_Req,
       lh.id1,
       lh.id2
FROM   gv$lock lw, gv$lock lh
WHERE  lh.id1     = lw.id1
AND    lh.id2     = lw.id2
AND    lh.request = 0
AND    lw.lmode   = 0
AND   (lh.id1, lh.id2) IN (
SELECT id1, id2 FROM gv$lock WHERE request = 0
INTERSECT
SELECT id1, id2 FROM gv$lock WHERE lmode   = 0);



LOCKING_INST LOCKING_SID WAITING_INST WAITER_SID WAITER_LOCK_TYPE      WAITER_MODE_REQ          ID1      ID2
------------ ----------- ------------ ---------- --------------------- ------------------- -------- --------
           1          17            1         34 Transaction           Exclusive             393241      698

this only applies if the blocker is on the same instance.
set linesize 300 
col kill for a70
col EVENT for a27
select con_id,sql_id,status,event,'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill from gv$session where sid in (select HOLDING_SESSION from dba_blockers);
 

set pagesize 10000

col object format a30
col cnt format 999,999,999

select O.owner||'.'||O.object_name object,      count(*) cnt
from
  gv$locked_object L,  dba_objects O
where 1=1
and  L.object_id = O.object_id
group by O.owner||'.'||O.object_name
order by 2;

set pagesize 1000 linesize 300

col "Session blocante" format a16
col "Session bloquee" format a16
col "Lock mode" format a13
col "Object" format a30
col "ctime" format a14

select L1.sid||' - '||L1.inst_id "Session blocante",
       decode(L1.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row-S (SS)',
         3, 'Row-X (SX)',
         4, 'Share (S)',
         5, 'S/Row-X (SSX)',
         6, 'Exclusive (X)',
         to_char(L1.lmode)) "Lock mode",
       (select O.owner||'.'||O.object_name from gv$locked_object LOBJ, dba_objects O where LOBJ.object_id=O.object_id and L1.sid=LOBJ.session_id and rownum<2) "Object",
       case when L1.ctime < 86400
         then to_char(to_date(L1.ctime, 'sssss'), 'hh24:mi:ss')
         else '+'||trunc(L1.ctime/86400)||' '||to_char(to_date(mod(L1.ctime, 86400), 'sssss'), 'hh24:mi:ss')
       end "ctime",
       L2.sid||' - '||L2.inst_id "Session bloquee",
       decode(L2.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row-S (SS)',
         3, 'Row-X (SX)',
         4, 'Share (S)',
         5, 'S/Row-X (SSX)',
         6, 'Exclusive (X)',
         to_char(L2.lmode)) "Lock mode",
       (select O.owner||'.'||O.object_name from gv$locked_object LOBJ, dba_objects O where LOBJ.object_id=O.object_id and L2.sid=LOBJ.session_id and rownum<2) "Object",
       case when L2.ctime < 86400
         then to_char(to_date(L2.ctime, 'sssss'), 'hh24:mi:ss')
         else '+'||trunc(L2.ctime/86400)||' '||to_char(to_date(mod(L2.ctime, 86400), 'sssss'), 'hh24:mi:ss')
       end "ctime"
from
  gv$lock L1,
  gv$lock L2
where
      L1.block=1
  and L2.request>0
  and L1.id1=L2.id1
  and L1.id2=L2.id2
order by L1.ctime;


col blocker_sid format 99999999999
col blocked_sid format 99999999999
col min_blocked format 99999999999
col request format 9999999
col sid_serial# format a50
select /*+ ORDERED */
'alter system kill session '''||blocker.lsid ||','||blocker.serial||',@'||blocker.INST_ID||''' immediate ;' sid_serial#
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request,blocker.sql_id
from (select l.sid lsid,se.serial# serial,l.id1 lid1,se.PREV_SQL_ID sql_id,se.INST_ID INST_ID
from v$lock l,gv$session se
where block != 0
and l.type = 'TX'
and se.sid=l.sid) blocker , v$lock blocked
where
blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.lid1;

SID_SERIAL#                                         BLOCKED_SID  MIN_BLOCKED  REQUEST SQL_ID
-------------------------------------------------- ------------ ------------ -------- -------------
alter system kill session '45,36,@1' immediate ;             17           43        6 18usygkws8pw9




col Kill_Command format a40
SELECT gvh.inst_id locking_inst, gvh.SID locking_sid,
       gvs.serial# locking_serial, gvs.status status, 
       --gvs.module module,
       gvw.inst_id waiting_inst, gvw.SID waiter_sid,
       DECODE (gvh.TYPE,
               'MR', 'Media_recovery',
               'RT', 'Redo_thread',
               'UN', 'User_name',
               'TX', 'Transaction',
               'TM', 'Dml',
               'UL', 'PLSQL User_lock',
               'DX', 'Distrted_Transaxion',
               'CF', 'Control_file',
               'IS', 'Instance_state',
               'FS', 'File_set',
               'IR', 'Instance_recovery',
               'ST', 'Diskspace Transaction',
               'IV', 'Libcache_invalidation',
               'LS', 'LogStaartORswitch',
               'RW', 'Row_wait',
               'SQ', 'Sequence_no',
               'TE', 'Extend_table',
               'TT', 'Temp_table',
               'Nothing-'
              ) waiter_lock_type,
       DECODE (gvw.request,
               0, 'None',
               1, 'NoLock',
               2, 'Row-Share',
               3, 'Row-Exclusive',
               4, 'Share-Table',
               5, 'Share-Row-Exclusive',
               6, 'Exclusive',
               'Nothing-'
              ) waiter_mode_req,
          'alter system kill session '
       || ''''
       || gvh.SID
       || ','
       || gvs.serial#
       || ''';' "Kill_Command"
  FROM gv$lock gvh, gv$lock gvw, gv$session gvs
 WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
                                FROM gv$lock
                               WHERE request = 0
                              INTERSECT
                              SELECT id1, id2
                                FROM gv$lock
                               WHERE lmode = 0)
   AND gvh.id1 = gvw.id1
   AND gvh.id2 = gvw.id2
   AND gvh.request = 0
   AND gvw.lmode = 0
   AND gvh.SID = gvs.SID
   AND gvh.inst_id = gvs.inst_id


LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS   WAITING_INST WAITER_SID WAITER_LOCK_TYPE      WAITER_MODE_REQ     Kill_Command
------------ ----------- -------------- -------- ------------ ---------- --------------------- ------------------- ----------------------------------------
           1          45             36 INACTIVE            1         17 Transaction           Exclusive           alter system kill session '45,36';

--Rac

col Kill_Command format a60
SELECT gvh.inst_id locking_inst, gvh.SID locking_sid,
       gvs.serial# locking_serial, gvs.status status, 
       --gvs.module module,
       gvw.inst_id waiting_inst, gvw.SID waiter_sid,
       DECODE (gvh.TYPE,
               'MR', 'Media_recovery',
               'RT', 'Redo_thread',
               'UN', 'User_name',
               'TX', 'Transaction',
               'TM', 'Dml',
               'UL', 'PLSQL User_lock',
               'DX', 'Distrted_Transaxion',
               'CF', 'Control_file',
               'IS', 'Instance_state',
               'FS', 'File_set',
               'IR', 'Instance_recovery',
               'ST', 'Diskspace Transaction',
               'IV', 'Libcache_invalidation',
               'LS', 'LogStaartORswitch',
               'RW', 'Row_wait',
               'SQ', 'Sequence_no',
               'TE', 'Extend_table',
               'TT', 'Temp_table',
               'Nothing-'
              ) waiter_lock_type,
       DECODE (gvw.request,
               0, 'None',
               1, 'NoLock',
               2, 'Row-Share',
               3, 'Row-Exclusive',
               4, 'Share-Table',
               5, 'Share-Row-Exclusive',
               6, 'Exclusive',
               'Nothing-'
              ) waiter_mode_req,
       -- 'alter system kill session '  || ''''  || gvh.SID || ',' || gvs.serial# || ''';' "Kill_Command"
		 'alter system  kill session ''' || gvh.sid || ',' || gvs.serial# || ',@' || gvh.inst_id || ''' immediate ; ' "Kill_Command"
  FROM gv$lock gvh, gv$lock gvw, gv$session gvs
 WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
                                FROM gv$lock
                               WHERE request = 0
                              INTERSECT
                              SELECT id1, id2
                                FROM gv$lock
                               WHERE lmode = 0)
   AND gvh.id1 = gvw.id1
   AND gvh.id2 = gvw.id2
   AND gvh.request = 0
   AND gvw.lmode = 0
   AND gvh.SID = gvs.SID
   AND gvh.inst_id = gvs.inst_id;

===

set linesize 300 pagesize 500
col username 	for a20 
col machine 	for a30
col event 		for a30
col sess 		for a20
col kill 		for a15
SELECT /*+ RULE */
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,
DECODE (request, 0, 'Holder: ', ' Waiter: ') || k.sid sess,
--k.inst_id,
ss.username,
--ss.sql_id,
nvl(ss.sql_id,ss.PREV_SQL_ID) sql_id,
k.id1,
k.id2,
k.lmode,
k.request,
k.TYPE,
SS.LAST_CALL_ET,
SS.SECONDS_IN_WAIT,
SS.SERIAL#,
SS.MACHINE,
SS.EVENT,
ss.status,
P.SPID,
CASE
WHEN request > 0
THEN
CHR (BITAND (p1, -16777216) / 16777215) || CHR (BITAND (p1, 16711680) / 65535)
ELSE
NULL
END "Name",
CASE WHEN request > 0 THEN (BITAND (p1, 65535)) ELSE NULL END "Mode"
FROM GV$LOCK k, gv$session ss, gv$process p
WHERE 1=1
and  (k.id1, k.id2, k.TYPE) IN (SELECT ll.id1, ll.id2, ll.TYPE FROM GV$LOCK ll
WHERE request > 0)
AND k.sid = ss.sid
AND K.INST_ID = SS.INST_ID
AND ss.paddr = p.addr
AND SS.INST_ID = P.INST_ID
ORDER BY id1, request;






set linesize 200
set lines 200 pages 1000 feed on verify off echo off
column sid          heading 'Sid'         format 99999
column serial_num   heading 'Serial#'     format 999999
column db_username  heading 'DB|Username' format a15 wrap
column process      heading 'Process'     format a10 truncate
column host_pid     heading 'Host|PID'    format a8  wrap
column machine      heading 'Host'        format a12 truncate
column program      heading 'Program'     format a25 truncate
column object_name  heading 'Object|Name' format a20 wrap
column lock_type    heading 'Lock|Type'   format a5 truncate
column mode_held    heading 'Mode|Held'   format a10 truncate
column mode_req     heading 'Mode|Requested' format a15 truncate
column ctime        heading 'Time Hrs.|Since|LMode|Held' format a8 truncate
column is_blocking  heading 'Blocking?'   format a12 wrap
break on sid on serial_num on process on db_username on spid on machine
select /*+rule */
ses.sid as sid,
ses.serial# as serial_num,
ses.process  as process,
ses.username as db_username,
pro.spid     as host_pid,
ses.machine  as machine,
substr(ses.program,1,30) as program,
substr(obj.object_name,1,20) as object_name,
loc.lock_type as lock_type,
loc.mode_held as mode_held,
loc.mode_requested as mode_req,
to_char(trunc(sysdate) + loc.last_convert/(24*3600), 'HH24:MI:SS') as ctime,
loc.blocking_others as is_blocking
from v$session ses,v$process pro,dba_lock loc,dba_objects obj
where ses.sid      = loc.session_id
and ses.paddr    = pro.addr
and loc.lock_id1 = obj.object_id
and ses.username is not null
order by ses.sid, ses.serial#, ses.process, ses.username
/
clear columns
clear breaks

                                                                                                                                              Since
                          DB              Host                                            Object               Lock  Mode       Mode            LMode
   Sid Serial# Process    Username        PID      Host         Program                   Name                 Type  Held       Requested       Held     Blocking?
------ ------- ---------- --------------- -------- ------------ ------------------------- -------------------- ----- ---------- --------------- -------- ------------
    17      20 4294       ANUJ            4301     anuj.kumarsi sqlplus@anuj.kumarsingh.c ORA$BASE             AE    Share      None            00:28:34 Not Blocking
                                          4301                  sqlplus@anuj.kumarsingh.c ANUJ                 DML   Row-X (SX) None            00:27:14 Not Blocking
    34      35 4357       ANUJ            4363     anuj.kumarsi sqlplus@anuj.kumarsingh.c ORA$BASE             AE    Share      None            00:26:27 Not Blocking
                                          4363                  sqlplus@anuj.kumarsingh.c ANUJ                 DML   Row-X (SX) None            00:24:18 Not Blocking
    45      26 4432       SYS             4442     anuj.kumarsi sqlplus@anuj.kumarsingh.c ORA$BASE             AE    Share      None            00:23:24 Not Blocking

5 rows selected.





prompt
prompt
col sid             heading 'Session|Id'  format 99999
col serial_num      heading 'Serial#'     format 999999
column db_username  heading 'DB|Username' format a15 wrap
column lock_id1                           format a8 noprint
column ctime   heading 'Time Hrs.|Since|LMode|Held' format a8 truncate
col sql        heading 'SQL|Text'    format a64 wrap
break on sid on serial_num on process on db_username
select /*+rule */
a.sid as sid,
a.serial#  as serial_num,
a.process  as process,
a.username as db_username,
c.lock_id1 as lock_id1,
to_char(trunc(sysdate) + c.last_convert/(24*3600), 'HH24:MI:SS') as ctime,b.sql_text sql
from   v$session a,v$sqltext b,dba_lock c
where  a.sid            = c.session_id
and    a.username is not null
and  a.username not in ('SYS','SYSTEM')
and   ((a.sql_address    = b.address
and  a.sql_hash_value = b.hash_value)
or    (a.prev_sql_addr  = b.address
and  a.prev_hash_value = b.hash_value))
order by a.sid, a.serial#, a.process, a.username, c.lock_id1, b.piece
/
clear breaks
clear columns


                                                         Time Hrs
                                                         Since
Session                                  DB              LMode    SQL
     Id Serial# PROCESS                  Username        Held     Text
------- ------- ------------------------ --------------- -------- ----------------------------------------------------------------
     17      20 4294                     ANUJ            00:31:05 update anuj set sal=1000 where EMPNO=7369
                                                         00:29:45 update anuj set sal=1000 where EMPNO=7369
                                                         00:29:45 update anuj set sal=1000 where EMPNO=7369
     34      35 4357                     ANUJ            00:28:58 update anuj set sal=1001 where EMPNO=7369
                                                         00:28:58 SELECT DECODE('A','A','1','2') FROM DUAL
                                                         00:26:49 SELECT DECODE('A','A','1','2') FROM DUAL
                                                         00:26:49 update anuj set sal=1001 where EMPNO=7369
                                                         00:26:49 SELECT DECODE('A','A','1','2') FROM DUAL
                                                         00:26:49 update anuj set sal=1001 where EMPNO=7369

9 rows selected.






col SQL_BLOCKING format a30
col SQL_BLOCKED format a30
col BLOCKING_STATUS format a30
col COMMAND_TYPE format a10
SELECT 
--l1.*, 
sq.sql_text sql_blocking, ss.sql_text sql_blocked,
--s1.SID sid_blocker, s2.SID sid_blocked, s1.osuser, s2.command,
       DECODE (s2.command,
               0, 'NO COMMAND',
               1, 'CREATE TABLE',
               2, 'INSERT',
               3, 'SELECT',
               4, 'CREATE CLUSTER',
               5, 'ALTER CLUSTER',
               6, 'UPDATE',
               7, 'DELETE',
               8, 'DROP CLUSTER',
               9, 'CREATE INDEX',
               10, 'DROP INDEX',
               11, 'ALTER INDEX',
               12, 'DROP TABLE',
               13, 'CREATE SEQUENCE',
               14, 'ALTER SEQUENCE',
               15, 'ALTER TABLE',
               16, 'DROP SEQUENCE',
               17, 'GRANT OBJECT',
               18, 'REVOKE OBJECT',
               19, 'CREATE SYNONYM',
               20, 'DROP SYNONYM',
               21, 'CREATE VIEW',
               22, 'DROP VIEW',
               23, 'VALIDATE INDEX',
               24, 'CREATE PROCEDURE',
               25, 'ALTER PROCEDURE',
               26, 'LOCK',
               27, 'NO-OP',
               28, 'RENAME',
               29, 'COMMENT',
               30, 'AUDIT OBJECT',
               31, 'NOAUDIT OBJECT',
               32, 'CREATE DATABASE LINK',
               33, 'DROP DATABASE LINK',
               34, 'CREATE DATABASE',
               35, 'ALTER DATABASE',
               36, 'CREATE ROLLBACK SEG',
               37, 'ALTER ROLLBACK SEG',
               38, 'DROP ROLLBACK SEG',
               39, 'CREATE TABLESPACE',
               40, 'ALTER TABLESPACE',
               41, 'DROP TABLESPACE',
               42, 'ALTER SESSION',
               43, 'ALTER USER',
               44, 'COMMIT',
               45, 'ROLLBACK',
               46, 'SAVEPOINT',
               47, 'PL/SQL EXECUTE',
               48, 'SET TRANSACTION',
               49, 'ALTER SYSTEM',
               50, 'EXPLAIN',
               51, 'CREATE USER',
               52, 'CREATE ROLE',
               53, 'DROP USER',
               54, 'DROP ROLE',
               55, 'SET ROLE',
               56, 'CREATE SCHEMA',
               57, 'CREATE CONTROL FILE',
               59, 'CREATE TRIGGER',
               60, 'ALTER TRIGGER',
               61, 'DROP TRIGGER',
               62, 'ANALYZE TABLE',
               63, 'ANALYZE INDEX',
               64, 'ANALYZE CLUSTER',
               65, 'CREATE PROFILE',
               66, 'DROP PROFILE',
               67, 'ALTER PROFILE',
               68, 'DROP PROCEDURE',
               70, 'ALTER RESOURCE COST',
               71, 'CREATE MATERIALIZED VIEW LOG',
               72, 'ALTER MATERIALIZED VIEW LOG',
               73, 'DROP MATERIALIZED VIEW LOG',
               74, 'CREATE MATERIALIZED VIEW',
               75, 'ALTER MATERIALIZED VIEW',
               76, 'DROP MATERIALIZED VIEW',
               77, 'CREATE TYPE',
               78, 'DROP TYPE',
               79, 'ALTER ROLE',
               80, 'ALTER TYPE',
               81, 'CREATE TYPE BODY',
               82, 'ALTER TYPE BODY',
               83, 'DROP TYPE BODY',
               84, 'DROP LIBRARY',
               85, 'TRUNCATE TABLE',
               86, 'TRUNCATE CLUSTER',
               91, 'CREATE FUNCTION',
               92, 'ALTER FUNCTION',
               93, 'DROP FUNCTION',
               94, 'CREATE PACKAGE',
               95, 'ALTER PACKAGE',
               96, 'DROP PACKAGE',
               97, 'CREATE PACKAGE BODY',
               98, 'ALTER PACKAGE BODY',
               99, 'DROP PACKAGE BODY',
               100, 'LOGON',
               101, 'LOGOFF',
               102, 'LOGOFF BY CLEANUP',
               103, 'SESSION REC',
               104, 'SYSTEM AUDIT',
               105, 'SYSTEM NOAUDIT',
               106, 'AUDIT DEFAULT',
               107, 'NOAUDIT DEFAULT',
               108, 'SYSTEM GRANT',
               109, 'SYSTEM REVOKE',
               110, 'CREATE PUBLIC SYNONYM',
               111, 'DROP PUBLIC SYNONYM',
               112, 'CREATE PUBLIC DATABASE LINK',
               113, 'DROP PUBLIC DATABASE LINK',
               114, 'GRANT ROLE',
               115, 'REVOKE ROLE',
               116, 'EXECUTE PROCEDURE',
               117, 'USER COMMENT',
               118, 'ENABLE TRIGGER',
               119, 'DISABLE TRIGGER',
               120, 'ENABLE ALL TRIGGERS',
               121, 'DISABLE ALL TRIGGERS',
               122, 'NETWORK ERROR',
               123, 'EXECUTE TYPE',
               157, 'CREATE DIRECTORY',
               158, 'DROP DIRECTORY',
               159, 'CREATE LIBRARY',
               160, 'CREATE JAVA',
               161, 'ALTER JAVA',
               162, 'DROP JAVA',
               163, 'CREATE OPERATOR',
               164, 'CREATE INDEXTYPE',
               165, 'DROP INDEXTYPE',
               167, 'DROP OPERATOR',
               168, 'ASSOCIATE STATISTICS',
               169, 'DISASSOCIATE STATISTICS',
               170, 'CALL METHOD',
               171, 'CREATE SUMMARY',
               172, 'ALTER SUMMARY',
               173, 'DROP SUMMARY',
               174, 'CREATE DIMENSION',
               175, 'ALTER DIMENSION',
               176, 'DROP DIMENSION',
               177, 'CREATE CONTEXT',
               178, 'DROP CONTEXT',
               179, 'ALTER OUTLINE',
               180, 'CREATE OUTLINE',
               181, 'DROP OUTLINE',
               182, 'UPDATE INDEXES',
               183, 'ALTER OPERATOR',
               '? : ' || s2.command
              ) command_type,
       s1.process, s1.machine, s1.terminal, s1.program, 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, v$sql ss, v$sql sq
 WHERE s1.SID = l1.SID
   AND s2.SID = l2.SID
   AND l1.BLOCK = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l1.id2 = l1.id2
   AND s2.sql_id = ss.sql_id
   AND s1.prev_sql_id = sq.sql_id
 


SQL_BLOCKING                   SQL_BLOCKED                    COMMAND_TY PROCESS         MACHINE         TERMINAL                       PROGRAM              BLOCKING_STATUS
------------------------------ ------------------------------ ---------- --------------- --------------- ------------------------------ ------------------------------------------------ --------------------
update anuj set SAL=1000 where update anuj set sal=1001 where UPDATE     10647           anuj.kumarsingh pts/1                          sqlplus@anuj.kumarsingh.co.uk (TNS V1-V3)     ANUJ@anuj.kumarsingh
 EMPNO='7369'                   EMPNO=7369                                                                                                                   .co.uk ( SID=45 ) is
                                                                                                                                                              blocking ANUJ@anuj.
                                                                                                                                                             kumarsingh.co.uk ( S
                                                                                                                                                             ID=17 )




==
Lock Object info



col OBJECT_NAME format a20
SELECT DO.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
       DBMS_ROWID.rowid_create (1,
                                row_wait_obj#,
                                row_wait_file#,
                                row_wait_block#,
                                row_wait_row#
                               ) rid
  FROM v$session s, dba_objects DO
 WHERE SID IN (
          SELECT s2.SID
            FROM v$lock l1,
                 v$session s1,
                 v$lock l2,
                 v$session s2,
                 v$sql ss,
                 v$sql sq
           WHERE s1.SID = l1.SID
             AND s2.SID = l2.SID
             AND l1.BLOCK = 1
             AND l2.request > 0
             AND l1.id1 = l2.id1
             AND l1.id2 = l1.id2
             AND s2.sql_id = ss.sql_id
             AND s1.prev_sql_id = sq.sql_id)
   AND s.row_wait_obj# = DO.object_id;


OBJECT_NAME          ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID
-------------------- ------------- -------------- --------------- ------------- ------------------
ANUJ                         73519              4             171             0 AAAR8vAAEAAAACrAAA

1 row selected.






set linesize 150
column sql_text      format a50   heading "SQL text"
column blocking_user format a8    Heading "Blocking|user"
column blocked_user  format a8    heading "Blocked|user"
column blocking_sid  format 9999  heading "Blocking|SID"
column blocked_sid   format 9999  heading "Blocked|SID"
column blocking_serial   format 9999  heading "Blocking|serial"
column type          format a4    heading "Lock|Type"
set pagesize 1000
set echo on 

WITH sessions AS 
       (SELECT /*+ materialize*/ username,sid,serial#,sql_id  FROM v$session), locks AS 
       (SELECT /*+ materialize */ *  FROM v$lock)
SELECT l2.type,s1.username blocking_user, s1.sid blocking_sid,s1.serial# blocking_serial, s2.username blocked_user, s2.sid blocked_sid, sq.sql_text
  FROM locks l1
  JOIN locks l2 USING (id1, id2)
  JOIN sessions s1 ON (s1.sid = l1.sid)
  JOIN sessions s2 ON (s2.sid = l2.sid)
  LEFT OUTER JOIN  v$sql sq
       ON (sq.sql_id = s2.sql_id)
 WHERE l1.BLOCK = 1 AND l2.request > 0;


Lock Blocking Blocking Blocking Blocked  Blocked
Type user          SID   serial user         SID SQL text
---- -------- -------- -------- -------- ------- --------------------------------------------------
TX   ANUJ           45       36 ANUJ          17 update anuj set sal=1001 where EMPNO=7369






COLUMN INST_ID                        HEADING "I#"                          FORMAT 99
COLUMN SID                                                                  FORMAT a20
COLUMN username                       HEADING "UserName"                    FORMAT a20 TRUNC
COLUMN lock_type                      HEADING "Lock|type"                   FORMAT a4  TRUNC
COLUMN lock_Name                      HEADING "Lock Name"                   FORMAT a22 TRUNC 
COLUMN osuser                         HEADING "OSUser"                      FORMAT a20 TRUNC
COLUMN machine                        HEADING "Client|Machine"              FORMAT a20 TRUNC 
COLUMN blocking_instance              HEADING "Bl'ing|Inst"         
COLUMN blocking_session               HEADING "Bl'ing|SID"
COLUMN blocking_session_status        HEADING "Bl'ing|Session|Status"
COLUMN final_blocking_instance        HEADING "Final|Bl'ing|Inst"
COLUMN final_blocking_session         HEADING "Final|Bl'ing|SID" 
COLUMN final_blocking_session_status  HEADING "Final|Bl'ing|Session|Status"
COLUMN program                        HEADING "Program"                     FORMAT a30 TRUNC
COLUMN event                          HEADING "Event"                       FORMAT a30 TRUNC


-- Materialize hint is needed to speed up the joins 
-- between dynamic performance view gv$session
 
-- Need to use Subquery for gv$session, otherwise session is disconnect with ORA-07445 [qksopCheckPropOpt]
WITH  sess as 
  ( select /*+ materialize */  s1.* from gv$session s1
  )
, locks as
(select /*+ materialize */ * from gv$lock)
SELECT s.inst_id
     , DECODE(level
           ,1 , s.sid || ' (Blocker)'
     , ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || s.sid 
     ) 
    -- || NVL2(s2.blocking_session, ' (Blocker)', ' ')
    sid
     , s.status
--  , l.lmode
--  , l.request
--  , l.block
--  , s.lock_type
  , lt.name lock_name
     , s.username
     , s.osuser
     , s.sql_id
     , s.program
     , s.event
--     , s.blocking_instance
--     , s.blocking_session
--     , s.blocking_session_status
--     , s.final_blocking_instance
--     , s.final_blocking_session
--     , s.final_blocking_session_status
FROM  sess s
      LEFT OUTER JOIN locks l ON  l.inst_id = s.inst_id
                         AND l.sid     = s.sid AND 
       --( l.lmode = 0 OR  (l.lmode > 0 AND l.block > 0))
        l.lmode = 0 
   LEFT OUTER JOIN v$lock_type lt ON l.type    = lt.type 
   LEFT OUTER JOIN sess s2 ON s.inst_id = s2.blocking_instance AND s.sid = s2.blocking_session
WHERE (
s.blocking_session IS NOT NULL
OR s2.blocking_session IS NOT NULL
)
CONNECT BY PRIOR s.inst_id = s.blocking_instance
       AND PRIOR s.sid     = s.blocking_session
START WITH s.blocking_session IS NULL
;


 I# SID                  STATUS   Lock Name              UserName             OSUser               SQL_ID        Program                        Event
--- -------------------- -------- ---------------------- -------------------- -------------------- ------------- ------------------------------ ------------------------------
  1 45 (Blocker)         INACTIVE                        ANUJ                 oracle                             sqlplus@anuj.kumarsingh.co.uk  SQL*Net message from client
  1  |---> 17            ACTIVE   Transaction            ANUJ                 oracle               186jyxf11m2qh sqlplus@anuj.kumarsingh.co.uk  enq: TX - row lock contention





set heading on 
COLUMN object_name FORMAT A30 
COLUMN holder FORMAT A25 
COLUMN Waiter FORMAT A25 
ttitle 'Blocking User and Waiting Users' 
select distinct o.object_name, sh.username||'('||sh.sid||','||sh.SERIAL#||')' "Holder", 
sw.username||'('||sw.sid||','||sw.SERIAL#||')' "Waiter", 
decode(lh.lmode, 1, 'null', 2, 
'row share', 3, 'row exclusive', 4, 'share', 
5, 'share row exclusive' , 6, 'exclusive') "Lock Type" 
from v$session sw, v$lock lw,all_objects o, v$session sh, v$lock lh 
where lh.id1 = o.object_id 
and lh.id1 = lw.id1 
and sh.sid = lh.sid 
and sw.sid = lw.sid 
and sh.lockwait is null 
and sw.lockwait is not null 
and lh.type = 'TM' 
and lw.type = 'TM'; 



OBJECT_NAME                    Holder                    Waiter                    Lock Type
------------------------------ ------------------------- ------------------------- -------------------
ANUJ                           ANUJ(45,36)               ANUJ(17,41)               row exclusive







Wait Info 


set linesize 140
set pagesize 100
clear column
col sid format a15
col serial format a10
col os_pid format a10
col username format a12
col osuser format a10
col program format a16
col status format a8
col event format a20
col sql_id format a15
select bl.blocking_tree "SID"
,      to_char(ps.serial#) "SERIAL"
,      ps.os_pid
,      ps.username
,      ps.osuser
,      ps.program
,      ps.status
,      ps.event
,      ps.sql_id
from
(select rownum rn, blocking_tree,locked from (with locks as (select blocking_session locker , sid locked from v$session
     where blocking_session is not null)
SELECT LPAD(locked, LENGTH(locked)+(level*2-2), ' ') blocking_tree, locked
FROM   (SELECT * FROM locks
        UNION
        SELECT NULL, locker
        FROM locks
        WHERE locker NOT IN (SELECT locked FROM locks))
CONNECT BY PRIOR locked = locker
START WITH locker IS NULL)) bl,
(select  s.sid
,       s.serial#
,       s.username
,       s.osuser
,       p.spid "OS_PID"
,       s.program
,       s.status
,       s.event
,       s.sql_id
from    v$session s
,       v$process p
Where   s.paddr = p.addr
order   by status,username) ps
where bl.locked=ps.sid
order by bl.rn

Sat May 19                                                                                                                         page    1
                                                      Blocking User and Waiting Users

SID             SERIAL     OS_PID     USERNAME     OSUSER     PROGRAM          STATUS   EVENT                SQL_ID
--------------- ---------- ---------- ------------ ---------- ---------------- -------- -------------------- ---------------
45              36         10653      ANUJ         oracle     sqlplus@anuj.kum INACTIVE SQL*Net message from
                                                              arsingh.co.uk (T           client
                                                              NS V1-V3)

  17            41         10717      ANUJ         oracle     sqlplus@anuj.kum ACTIVE   enq: TX - row lock c 186jyxf11m2qh
                                                              arsingh.co.uk (T          ontention
                                                              NS V1-V3)


col SQL_TEXT format a50
col sid format 99999
select s.sid, s.serial#, s.username, 
-- s.module, 
s.ROW_WAIT_OBJ# object_id,
dbms_rowid.rowid_create(1, s.row_wait_obj#, s.row_wait_file#,
s.row_wait_block#, s.row_wait_row#) my_rowid,
s.sql_hash_value, s.sql_address, sq.SQL_TEXT
from v$session_wait sw, v$session s, v$sql sq, v$lock l
where 1=1
--and sw.event = 'enqueue'
and sw.sid = s.sid
and l.type = 'TX' and l.request = 6
and l.sid = s.sid
and s.sql_hash_value = sq.hash_value and s.sql_address = sq.address


   SID    SERIAL# USERNAME      OBJECT_ID MY_ROWID           SQL_HASH_VALUE SQL_ADDR SQL_TEXT
------ ---------- ------------ ---------- ------------------ -------------- -------- --------------------------------------------------
    17         41 ANUJ              73519 AAAR8vAAEAAAACrAAA     2182712016 3B7D9708 update anuj set sal=1001 where EMPNO=7369






SQL> r
  1  select      nvl(S.USERNAME,'Internal') username,
  2        nvl(S.TERMINAL,'None') terminal,
  3          nvl(S.MACHINE,'None') machine,
  4          nvl(S.OSUSER,'None') OSuser,
  5        L.SID||','||S.SERIAL# Kill,
  6        U1.NAME||'.'||substr(T1.NAME,1,20) tab,
  7        decode(L.LMODE,1,'No Lock',
  8              2,'Row Share',
  9              3,'Row Exclusive',
 10              4,'Share',
 11              5,'Share Row Exclusive',
 12              6,'Exclusive',null) lmode,
 13        decode(L.REQUEST,1,'No Lock',
 14              2,'Row Share',
 15              3,'Row Exclusive',
 16              4,'Share',
 17              5,'Share Row Exclusive',
 18              6,'Exclusive',null) request
 19  from      V$LOCK L,
 20        V$SESSION S,
 21        SYS.USER$ U1,
 22        SYS.OBJ$ T1
 23  where      L.SID = S.SID
 24  and      T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
 25  and      U1.USER# = T1.OWNER#
 26  and      S.TYPE != 'BACKGROUND'
 27* order by   username, terminal, machine, lmode

Username   Term       Machine    OSuser          Kill String   Table Name                          Lock Held            Lock Requested
---------- ---------- ---------- --------------- ------------- ----------------------------------- -------------------- --------------------
ANUJ       pts/1      anuj.kumar oracle          17,20         SYS.STREAMS$_CAPTURE_PRO            Exclusive
                      singh.co.u
                      k

                      anuj.kumar oracle                        ANUJ.ANUJ                           Row Exclusive
                      singh.co.u
                      k

                      anuj.kumar oracle                        SYS.ORA$BASE                        Share
                      singh.co.u
                      k

ANUJ       pts/2      anuj.kumar oracle          34,35         ANUJ.ANUJ                           Row Exclusive
                      singh.co.u
                      k

                      anuj.kumar oracle                        SYS.ORA$BASE                        Share
                      singh.co.u
                      k

                      anuj.kumar oracle                        SYS.STREAMS$_CAPTURE_PRO                                 Exclusive
                      singh.co.u
                      k

SYS        pts/3      anuj.kumar oracle          45,26         SYS.ORA$BASE                        Share
                      singh.co.u
                      k


7 rows selected.

=====

from https://github.com/iusoltsev/sqlplus/blob/master/ash_lock3.sql


define 1="2021-09-15 01:15:27"
define 2="2021-09-15 04:15:27"
set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col LVL for a3
col BLOCKING_TREE for a20
col SQL_TEXT for a100
col EVENT for a34
col Lock_Mode for a12
col BLOCKER for a12
col OBJECT_NAME for a40
col SQL_PLAN_OPERATION for a50

with ash as (select * from gv$active_session_history
             where sample_time between to_date('&1','yyyy-mm-dd hh24:mi:ss')
                                   and to_date('&2','yyyy-mm-dd hh24:mi:ss'))
select to_char(LEVEL) as LVL,
       LPAD(' ',(LEVEL-1)*2)||session_id||','||session_serial# as BLOCKING_TREE,
       to_char(min(sample_time),'hh24:mi:ss') as START_WTIME,
       to_char(max(sample_time),'hh24:mi:ss') as STOP_WTIME,
       xid,
--sql_exec_id,
ash.sql_id,
       decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue') as EVENT,
       decode(event, 'enq: TM - contention', chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535)||' '||bitand(p1, 65535), '') as "Lock_Mode",
       blocking_session||','||blocking_session_serial# as BLOCKER,
       o.object_type || ' ' || o.object_name as OBJECT_NAME,
       count(distinct sample_id) as WAITS_COUNT,
       sql_plan_operation||' '||sql_plan_options as SQL_PLAN_OPERATION,
       trim(replace(replace(dbms_lob.substr(sql_text,100),chr(10)),chr(9))) as sql_text
  from ash
       left join dba_hist_sqltext hs on ash.sql_id = hs.sql_id
       left join dba_objects o on decode(event, 'enq: TM - contention', ash.p2, ash.current_obj#) = o.object_id
 where session_state = 'WAITING'
       start with BLOCKING_SESSION is not null and session_serial# > 1
             and event not like 'log file sync'
connect by nocycle ash.SAMPLE_ID  = prior ash.SAMPLE_ID
               and ash.SESSION_ID = prior ash.BLOCKING_SESSION
 group by LEVEL,
          LPAD(' ',(LEVEL-1)*2)||session_id||','||session_serial#,
          xid,
--sql_exec_id,
ash.sql_id,
          decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue'),
          decode(event, 'enq: TM - contention', chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535)||' '||bitand(p1, 65535), ''),
          blocking_session||','||blocking_session_serial#,
          o.object_type || ' ' || o.object_name,
          hs.sql_id,
          sql_plan_operation||' '||sql_plan_options,
          trim(replace(replace(dbms_lob.substr(sql_text,100),chr(10)),chr(9)))
 order by LEVEL, trim(LPAD(' ',(LEVEL-1)*2)||session_id||','||session_serial#), min(sample_time)
/
set feedback on echo off VERIFY ON

=====


set linesize 200
col SQLTEXT format a70
SELECT  '(SID='||lpad(s1.sid,4,' ')||')' blocker,  l1.ctime btime1,
        '(SID='||lpad(s2.sid,4,' ')||')' blocking, a2.sql_text sqltext, l2.ctime btime2
FROM    v$lock l1, v$session s1, v$lock l2, v$session s2, v$sqlarea a1, v$sqlarea a2
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
AND     l1.sid != l2.sid
AND     s1.sql_address = a1.address (+)
AND     s2.sql_address = a2.address (+)
ORDER BY btime2, blocker, blocking

BLOCKER        BTIME1 BLOCKING   SQLTEXT                                                                    BTIME2
---------- ---------- ---------- ---------------------------------------------------------------------- ----------
(SID=  17)       1562 (SID=  34) update anuj set sal=1001 where EMPNO=7369                                    1386


=============

set linesize 200 pagesize 200
col RUNNING_SESSION  for a20
col MACHINE   for a22
col EVENT   for a30
col USERNAME   for a12
col OSUSER   for a12
col WAIT_CLASS   for a12
col kill for a15
WITH
 -- global lock view
 gl AS 
(
 select  inst_id || '-' || sid instsid, id1, id2, ctime, lmode, block, request  from  gv$lock
 ),
 -- joins the global lock view on itself to identify locks
 l AS (
 SELECT l1.instsid holding_session, l2.instsid waiting_session FROM gl l1, gl l2
 WHERE  l1.block > 0
 AND l2.request > 0
 AND l1.id1=l2.id1
 AND l1.id2=l2.id2
 ),
 -- result view (tree of locked sessions)
 rs AS (
 SELECT
 lpad(' ',3*(level-1),' ') || waiting_session running_session
 FROM (
 -- first insert as in utllockt
 (SELECT  '-' holding_session, holding_session waiting_session
 FROM  l
 MINUS
 SELECT  '-', waiting_session  FROM  l
 )
 UNION ALL
 -- second insert as in utllockt
 SELECT holding_session, waiting_session FROM  l  )
 CONNECT BY PRIOR waiting_session = holding_session
 START WITH  holding_session = '-'
 ),
 -- useful session informations
 s AS (
 SELECT  
inst_id, sid,
machine, osuser, username, nvl(sql_id, '-') sql_id,PREV_SQL_ID, event, wait_class, ''''||sid||','||serial#||',@'||inst_id||'''' kill FROM gv$session
 )
 -- final tree
 SELECT  *  FROM  rs
 JOIN
 s ON ltrim(rs.running_session)=s.inst_id || '-' || s.sid;

RUNNING_SESSION         INST_ID        SID MACHINE                OSUSER       USERNAME     SQL_ID        PREV_SQL_ID   EVENT                          WAIT_CLASS   KILL
-------------------- ---------- ---------- ---------------------- ------------ ------------ ------------- ------------- ------------------------------ ------------ ---------------
1-43                          1         43 oraasm12c.localdomain  oracle       SYS          8xn14nkvsjb48 8xn14nkvsjb48 SQL*Net message from client    Idle         '43,20232,@1'
   1-42                       1         42 oraasm12c.localdomain  oracle       SYS          f602ubc924gxr 1uby466jrc2yw enq: TX - row lock contention  Application  '42,13762,@1'



SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
43


for session 43 ... 
SQL> update dual set DUMMY=2;

1 row updated.

commit this session or kill this session 
=====================

SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
42

for session 43 ... 
update dual set DUMMY=2;  --- This session is hagging

======

set linesize 300

col blocking_session for a50

col blocked_session for a50

col blocking_session_kill for a70

select blocking_session,blocked_session,script blocking_session_kill from

( select distinct

s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,

s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,

decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null) script ,count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

where s1.sid=l1.sid

and s2.sid=l2.sid

and s1.inst_id=l1.inst_id

and s2.inst_id=l2.inst_id

and l1.block > 0

and l2.request > 0

and l1.id1 = l2.id1

and l1.id2 = l2.id2 )

order by blocked_cnt desc;

--Pluggable database

set linesize 300 pagesize 300

col blkg_user for a15

col wait_machine for a20

col kill_string for a15

col obj_name for a20

col blkg_machine for a20

col wait_user for a20

col obj_own for a20

col kill for a15

col kill2 for a15

col name for a15

SELECT s1.username blkg_user ,s1.machine blkg_machine

,s1.sid blkg_sid ,s1.serial# blkg_serialnum

,''''||s1.sid ||','|| s1.serial#||',@'||s1.inst_id ||'''' kill

--,s1.sid || ',' || s1.serial# kill_string

,s1.con_id,u.name ,NVL(s1.sql_id, s1.prev_sql_id) sql_id1

,s2.username wait_user,s2.machine wait_machine

,s2.sid wait_sid,s2.serial# wait_serialnum,s2.con_id,NVL(s2.sql_id, s2.prev_sql_id) sql_id2

,''''||s2.sid ||','|| s2.serial#||',@'||s2.inst_id ||'''' kill2

,lo.object_id blkd_obj_id,do.owner obj_own

,do.object_name obj_name

FROM gv$lock l1,gv$session s1,gv$lock l2,gv$session s2,gv$locked_object lo,cdb_objects do,gv$containers u

WHERE 1=1

and s1.sid = l1.sid and s2.sid = l2.sid

and s1.inst_id = l1.inst_id and s2.inst_id = l2.inst_id

and l1.id1 = l2.id1 and s1.sid = lo.session_id

and l1.inst_id = l2.inst_id and s1.inst_id = lo.inst_id

and lo.object_id = do.object_id

and l1.block = 1

and l2.request > 0

and do.con_id = u.con_id

and s1.inst_id = u.inst_id;

============

set linesize 300

col BLOCKING_TREE for a65

col SQL_TEXT for a60

col EVENT for a34

with L as

(select--+ materialize

L1.inst_id, L1.sid

from gv$lock L1, gv$lock L2

where L1.block > 0

and L1.ID1 = L2.ID1

and L1.ID2 = L2.ID2

and L2.type = 'TX'

and L2.REQUEST > 0)

select--+ opt_param('_connect_by_use_union_all' 'false')

LPAD(' ', (LEVEL - 1) * 2) || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || program as BLOCKING_TREE,

EVENT,

last_call_et,

blocking_session_status,

s.sql_id,

decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid,substr(trim(NVL(sa1.sql_text,sa2.sql_text)), 1, 60) SQL_TEXT

-- prior status

from gv$session s, gv$sqlarea sa1, gv$sqlarea sa2

where s.sql_id = sa1.sql_id(+)

and s.inst_id = sa1.inst_id(+)

and s.prev_sql_id = sa2.sql_id(+)

and s.inst_id = sa2.inst_id(+)

connect by prior sid = blocking_session

start with (s.inst_id, s.sid) in (select inst_id, sid from L) ;

set linesize 300 pagesize 300

col SQL_TEXT for a60

col EVENT for a30

col BLOCKING_TREE for a65

select

LPAD(' ',(LEVEL-1)*2)||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || program as BLOCKING_TREE,

EVENT,

last_call_et,

blocking_session_status,

s.sql_id,

decode(sign(nvl(s.ROW_WAIT_OBJ#,-1)),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# )) req_rowid,substr(trim(sa.sql_text),1,60) SQL_TEXT

from gv$session s, gv$sqlarea sa

where s.sql_id = sa.sql_id(+)

connect by prior sid = blocking_session

start with s.sid in (select sid from gv$lock where type = 'TX' and block > 0);

=====

set linesize 300 pagesize 300

col SIDS for a25

col P1TEXT for a15

col P2TEXT for a15

col P3TEXT for a15

col p1 for 99999999999

set numf 99999999999999999

with sum_by_session as (

select ash.sql_id, ash.session_id,

trunc(ash.sample_time,'HH') timeframe, min(ash.sample_time) min_time, max(ash.sample_time) max_time, sum(ash.wait_time) + sum(ash.time_waited) total_wait,

ash.event, ash.p1, ash.p1text, ash.p2, ash.p2text, ash.p3, ash.p3text, ash.in_hard_parse

from gv$active_session_history ash

join gv$active_session_history sqlids on sqlids.sql_id = ash.sql_id

where (ash.event like 'cursor: pin S%' or ash.in_hard_parse = 'Y' )

-- and sqlids.event = 'cursor: pin S wait on X'

and sqlids.event = 'enq: TX - row lock contention'

group by ash.sql_id, ash.session_id, ash.event, ash.p1, ash.p1text, ash.p2, ash.p2text, ash.p3, ash.p3text, ash.in_hard_parse,trunc(ash.sample_time,'HH')

)

select s.sql_id, to_char(s.timeframe,'dd-Mon-RR HH24') timeframe, to_char(min(s.min_time),'HH24:MI:SS')||'-'||to_char(max(s.max_time),'HH24:MI:SS') timeperiod,

round(sum(total_wait)/1000000,2) total_wait_in_s, s.event, s.p1, s.p1text, s.p2, s.p2text, s.p3, s.p3text, s.in_hard_parse, listagg(s.session_id,',') within group (order by s.session_id) as sids

from sum_by_session s

group by s.sql_id, s.event, s.p1, s.p1text, s.p2, s.p2text, s.p3, s.p3text, s.in_hard_parse, s.timeframe

order by s.sql_id, s.in_hard_parse desc, s.timeframe;

===

set linesize 300

col USERNAME for a20

col MACHINE for a30

col EVENT for a30

col SESS for a20

col kill for a15

SELECT /*+ RULE */

''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,

DECODE (request, 0, 'Holder: ', ' Waiter: ') || k.sid sess,

--k.inst_id,

ss.username,

ss.sql_id,

k.id1,

k.id2,

k.lmode,

k.request,

k.TYPE,

SS.LAST_CALL_ET,SS.SECONDS_IN_WAIT,

SS.SERIAL#,

SS.MACHINE,

SS.EVENT,

ss.status,

P.SPID,

CASE

WHEN request > 0

THEN

CHR (BITAND (p1, -16777216) / 16777215) || CHR (BITAND (p1, 16711680) / 65535)

ELSE

NULL

END "Name",

CASE WHEN request > 0 THEN (BITAND (p1, 65535)) ELSE NULL END "Mode"

FROM GV$LOCK k, gv$session ss, gv$process p

WHERE 1=1

and (k.id1, k.id2, k.TYPE) IN (SELECT ll.id1, ll.id2, ll.TYPE FROM GV$LOCK ll

WHERE request > 0)

AND k.sid = ss.sid

AND K.INST_ID = SS.INST_ID

AND ss.paddr = p.addr

AND SS.INST_ID = P.INST_ID

ORDER BY id1, request;

===

to kill

col kill for a70

-- spool kill.sql

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill from GV$SESSION

where 1=1

and sid in (select distinct final_blocking_session from gv$session

where 1=1

--and event='cursor: pin S wait on X'

and event='enq: TX - row lock contention'

)

;

==============

from web ..

set linesize 1000 pagesize 300

col blocking_tree for a25

col username for a15

col p1text for a15

col p2text for a15

col sql_text for a50 wrap

col osuser for a15

col event for a30

with

LOCKS as (select /* + MATERIALIZE */ * from gv$lock),

S as (select /* + MATERIALIZE */ s. * From gv$session s),

BLOCKERS as (select distinct L1.inst_id, L1.sid

from LOCKS L1, LOCKS L2

where L1.block> 0

and L1.ID1 = L2.ID1

and L1.ID2 = L2.ID2

and L2.REQUEST> 0),

WAITERS as (select inst_id, sid from S where blocking_session is not null or blocking_instance is not null)

select LPAD (' ', (LEVEL - 1) * 2) || 'Kill-#' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' as BLOCKING_TREE,

s.program,

substr(s.USERNAME || '' || s.CLIENT_IDENTIFIER, 1.40) as USERNAME,

EVENT,

last_call_et,

seconds_in_wait as SECS_IN_WAIT,

blocking_session_status as BLOCK_SESSTAT,

pdml_enabled,

s.sql_id,

s.osuser,

p.spid,

s.machine as CLNT_HOST,

s.process as CLNT_PID,

s.port as CLNT_PORT,

substr(trim (NVL (sa1.sql_text, sa2.sql_text)), 1, 100) SQL_TEXT,

decode(sign (nvl (s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE (1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid,

p1text || '' || decode(p1text, 'name | mode', chr(bitand (p1, -16777216)/16777215) || chr (bitand(p1, 16711680) / 65535) || '' || bitand(p1, 65535), p1text) as

p1text,

p1,

p1raw,

p2text || '' || decode (p2text, 'object#', o.object_name || '' || o.owner || '.' || o.object_name, p2text) as p2text,

from s

left join gv$sqlarea sa1 on s.sql_id = sa1.sql_id and s.inst_id = sa1.inst_id

left join gv$sqlarea sa2 on s.prev_sql_id = sa2.sql_id and s.inst_id = sa2.inst_id

left join dba_objects o on s.p2 = o.object_id

left join gv$process p on s.paddr = p.addr and s.inst_id = p.inst_id

connect by NOCYCLE prior sid = blocking_session and prior s.inst_id = blocking_instance

start with (s.inst_id, s.sid) in (select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS);

===================

set linesize 300 pagesize 300

col username for a25

col event for a30

SELECT lpad(' ',level) || ''''||sid ||','|| serial#||',@'||inst_id ||''''||USERNAME AS username, blocking_session, terminal, event, sql_id,prev_sql_id, status, seconds_in_wait

,con_id

FROM gv$session s

START WITH blocking_session IS NULL

AND EXISTS

(SELECT 1

FROM gv$session i_s

WHERE i_s.blocking_session = s.sid

AND i_s.blocking_instance = s.inst_id

)

CONNECT BY blocking_session = PRIOR sid

AND blocking_instance = PRIOR inst_id;

===================

Object Lock info ..

SQL> LOCK TABLE emp IN EXCLUSIVE MODE;

LOCK TABLE emp IN EXCLUSIVE MODE

ERROR at line 1:

ORA-00069: cannot acquire lock -- table locks disabled for EMP

SQL> alter table emp enable table lock;

Table altered.

SQL> LOCK TABLE emp IN EXCLUSIVE MODE;

set linesize 500 pagesize 500

col kill for a15

col user_name for a20

col osuser for a20

col obj_owner for a20

col object_name for a20

col machine for a25

col mode_held for a15

col lock_type for a12

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.sql_id,prev_sql_id,username user_name, owner obj_owner,object_name, object_type, s.osuser, s.machine,

DECODE(l.block,

0, 'Not Blocking',

1, 'Blocking',

2, 'Global') STATUS,

DECODE(v.locked_mode,

0, 'None',

1, 'Null',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share',

5, 'S/Row-X (SSX)',

6, 'Exclusive', TO_CHAR(lmode)

) MODE_HELD,

decode(l.TYPE,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',l.type) lock_type

FROM gv$locked_object v, dba_objects d,gv$lock l, gv$session s

WHERE v.object_id = d.object_id

AND (v.object_id = l.id1)

AND v.session_id = s.sid

--and object_name like upper('%TEST%')

--and username like upper('ANUJ')

ORDER BY username, session_id;

KILL SQL_ID PREV_SQL_ID USER_NAME OBJ_OWNER OBJECT_NAME OBJECT_TYPE OSUSER MACHINE STATUS MODE_HELD LOCK_TYPE

'2461,36114,@2' arzfbandtq5ma 92nb6gtr9f56g SYS SYS EMP TABLE oracle XXrac02.xxx.xxx.xxxxxxxx. Global Exclusive DML

=============================

set headingsep ='|'

set lines 160

set pagesize 20

ttitle 'Database Locking Conflict Report'

btitle 'Mode Held = indicates the user holding the lock|Mode Request = indicates the user waiting on the later to finish to establish lock||** End of Locking Conflict Report **'

column username format a10 heading 'User'

column terminal format a15 heading 'Application|PC'

column object format a15 heading 'Table'

column sql format a15 heading 'SQL'

column sid format 999 heading 'SID'

column lock_type format a15 heading 'Lock|Type'

column mode_held format a11 heading 'Mode|Held'

column mode_requested format a10 heading 'Mode|Request'

column lock_id1 format a8 heading 'Lock ID1'

column lock_id2 format a8 heading 'Lock ID2'

column first_load_time format a19 heading 'Requested'

col kill for a15

break on lock_id1

select ''''||gv$session.sid ||','|| gv$session.serial#||',@'||gv$session.inst_id ||'''' kill,

username,

terminal,

decode(a.type,'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table', a.type) lock_type,

decode(a.lmode,0, 'None', /* Mon Lock equivalent */

1, 'Null', /* N */

2, 'Row-S (SS)', /* L */

3, 'Row-X (SX)', /* R */

4, 'Share', /* S */

5, 'S/Row-X (SSX)', /* C */

6, 'Exclusive', /* X */

to_char(a.lmode)) mode_held,

decode(a.request,

0, 'None', /* Mon Lock equivalent */

1, 'Null', /* N */

2, 'Row-S (SS)', /* L */

3, 'Row-X (SX)', /* R */

4, 'Share', /* S */

5, 'S/Row-X (SSX)', /* C */

6, 'Exclusive', /* X */

to_char(a.request)) mode_requested,

to_char(a.id1) lock_id1, to_char(a.id2) lock_id2,

c.object object,

d.sql_text sql,

e.first_load_time

from gv$lock a, gv$session, v$access c, gv$sqltext d, gv$sqlarea e

where (id1,id2) in (select b.id1, b.id2 from gv$lock b where b.id1=a.id1 and b.id2=a.id2 and b.request>0)

and a.sid = gv$session.sid

and a.inst_id = gv$session.inst_id

and a.sid = c.sid

-- and a.inst_id = c.inst_id

and d.address = gv$session.sql_address

and d.inst_id = gv$session.inst_id

and d.hash_value = gv$session.sql_hash_value

and d.address = e.address

and d.inst_id = e.inst_id

order by a.id1, a.lmode desc;

=============================

set linesize 500 pagesize 300

col username for a20

col module for a20

col object_name for a20

col kill_command for a55

col client_identifier for a15

col EVENT for a15

select gvh.inst_id locking_inst, round ( (gvs.seconds_in_wait/60),2) Mins_in_wait, gvh.sid Locking_Sid, gvs.serial# Locking_Serial,

gvs.username,NVL(gvs.sql_id,gvs.prev_sql_id) as SQL_ID,

gvs.status status,

gvs.module module,

gvw.inst_id waiting_inst,

gvw.sid waiter_sid,

gvs.client_identifier,

gvs.event,

decode(gvh.type, 'MR', 'Media_recovery',

'RT', 'Redo_thread',

'UN', 'User_name',

'TX', 'Transaction',

'TM', 'Dml',

'UL', 'PLSQL User_lock',

'DX', 'Distrted_Transaxion',

'CF', 'Control_file',

'IS', 'Instance_state',

'FS', 'File_set',

'IR', 'Instance_recovery',

'ST', 'Diskspace Transaction',

'IV', 'Libcache_invalidation',

'LS', 'LogStaartORswitch',

'RW', 'Row_wait',

'SQ', 'Sequence_no',

'TE', 'Extend_table',

'TT', 'Temp_table',

'Nothing-') Waiter_Lock_Type,

decode(gvw.request, 0, 'None',

1, 'NoLock',

2, 'Row-Share',

3, 'Row-Exclusive',

4, 'Share-Table',

5, 'Share-Row-Exclusive',

6, 'Exclusive',

'Nothing-') Waiter_Mode_Req ,

dobj.object_name,

'alter system kill session '''||gvs.sid||','||gvs.serial#||',@'||gvs.inst_id||''' immediate ;' "Kill_Command"

FROM gv$lock gvh, gv$lock gvw, gv$session gvs, dba_objects dobj

WHERE (gvh.id1, gvh.id2) in (

SELECT id1, id2 FROM gv$lock WHERE request=0

INTERSECT

SELECT id1, id2 FROM gv$lock WHERE lmode=0)

and gvh.id1=gvw.id1

and gvh.id2=gvw.id2

and gvh.request=0

and gvw.lmode=0

and gvh.sid=gvs.sid

and gvh.inst_id=gvs.inst_id

and gvs.row_wait_obj# = dobj.object_id

;

LOCKING_INST MINS_IN_WAIT LOCKING_SID LOCKING_SERIAL USERNAME SQL_ID STATUS MODULE WAITING_INST WAITER_SID CLIENT_IDENTIFI EVENT WAITER_LOCK_TYPE WAITER_MODE_REQ OBJECT_NAME Kill_Command

2 28.33 2274 28853 SYS c9081agx99wdx INACTIVE sqlplus@rac02.int. 2 9 SQL*Net message Transaction Exclusive PK_EMP alter system kill session '2274,28853,@2' immediate ;

======================

set linesize 300

col username for a20

col sql_text for a50 wrap

col kill for a15

col event for a35

with s as (

SELECT

''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' as kill,decode(level,1,'Blocking..','Waiting') state,

LPAD('**',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,decode(status,'INACTIVE',prev_sql_id,sql_id) sql_id,inst_id,event

FROM gv$session s

where level > 1 or exists (select null from gv$session where blocking_session = s.sid)

connect by prior s.sid = s.blocking_session

start with s.blocking_session is null

)

select kill ,s.state, s.username,event, s.sql_id, sql_text from gv$sqlarea a, s

where a.sql_id=s.sql_id

and a.inst_id=s.inst_id

order by 1,2

;

KILL STATE USERNAME EVENT SQL_ID SQL_TEXT

--------------- ---------- -------------------- ----------------------------------- ------------- --------------------------------------------------

'2271,344,@2' Blocking.. SYS SQL*Net message from client a8brh3sqdrd56 update emp set COMM=80 where EMPNO=7369

'7,56865,@2' Waiting *SYS enq: TX - row lock contention a8brh3sqdrd56 update emp set COMM=80 where EMPNO=7369

====

set lines 300 pagesize 66

break on Kill on sid on username on terminal

column Kill heading 'Kill String' format a13

column res heading 'Resource Type' format 999

column id1 format 9999990

column id2 format 9999990

column locking heading 'Lock Held/Lock Requested' format a40

column lmode heading 'Lock Held' format a20

column request heading 'Lock Requested' format a20

column serial# format 99999

column username format a10 heading "Username"

column terminal heading Term format a6

column tab format a30 heading "Table Name"

column owner format a9

column LAddr heading "ID1 - ID2" format a18

column Lockt heading "Lock Type" format a40

column command format a25

column sid format 990

col kill for a16

select

''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,

nvl(S.USERNAME,'Internal') username,

--L.SID,

nvl(S.TERMINAL,'None') terminal,

decode(command,

0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,

decode(command,

0,'BACKGROUND',

1,'Create Table',

2,'INSERT',

3,'SELECT',

4,'CREATE CLUSTER',

5,'ALTER CLUSTER',

6,'UPDATE',

7,'DELETE',

8,'DROP',

9,'CREATE INDEX',

10,'DROP INDEX',

11,'ALTER INDEX',

12,'DROP TABLE',

13,'CREATE SEQUENCE',

14,'ALTER SEQUENCE',

15,'ALTER TABLE',

16,'DROP SEQUENCE',

17,'GRANT',

18,'REVOKE',

19,'CREATE SYNONYM',

20,'DROP SYNONYM',

21,'CREATE VIEW',

22,'DROP VIEW',

23,'VALIDATE INDEX',

24,'CREATE PROCEDURE',

25,'ALTER PROCEDURE',

26,'LOCK TABLE',

27,'NO OPERATION',

28,'RENAME',

29,'COMMENT',

30,'AUDIT',

31,'NOAUDIT',

32,'CREATE EXTERNAL DATABASE',

33,'DROP EXTERNAL DATABASE',

34,'CREATE DATABASE',

35,'ALTER DATABASE',

36,'CREATE ROLLBACK SEGMENT',

37,'ALTER ROLLBACK SEGMENT',

38,'DROP ROLLBACK SEGMENT',

39,'CREATE TABLESPACE',

40,'ALTER TABLESPACE',

41,'DROP TABLESPACE',

42,'ALTER SESSION',

43,'ALTER USER',

44,'COMMIT',

45,'ROLLBACK',

46,'SAVEPOINT',

47,'PL/SQL EXECUTE',

48,'SET TRANSACTION',

49,'ALTER SYSTEM SWITCH LOG',

50,'EXPLAIN',

51,'CREATE USER',

52,'CREATE ROLE',

53,'DROP USER',

54,'DROP ROLE',

55,'SET ROLE',

56,'CREATE SCHEMA',

57,'CREATE CONTROL FILE',

58,'ALTER TRACING',

59,'CREATE TRIGGER',

60,'ALTER TRIGGER',

61,'DROP TRIGGER',

62,'ANALYZE TABLE',

63,'ANALYZE INDEX',

64,'ANALYZE CLUSTER',

65,'CREATE PROFILE',

66,'DROP PROFILE',

67,'ALTER PROFILE',

68,'DROP PROCEDURE',

69,'DROP PROCEDURE',

70,'ALTER RESOURCE COST',

71,'CREATE SNAPSHOT LOG',

72,'ALTER SNAPSHOT LOG',

73,'DROP SNAPSHOT LOG',

74,'CREATE SNAPSHOT',

75,'ALTER SNAPSHOT',

76,'DROP SNAPSHOT',

79,'ALTER ROLE',

85,'TRUNCATE TABLE',

86,'TRUNCATE CLUSTER',

87,'-',

88,'ALTER VIEW',

89,'-',

90,'-',

91,'CREATE FUNCTION',

92,'ALTER FUNCTION',

93,'DROP FUNCTION',

94,'CREATE PACKAGE',

95,'ALTER PACKAGE',

96,'DROP PACKAGE',

97,'CREATE PACKAGE BODY',

98,'ALTER PACKAGE BODY',

99,'DROP PACKAGE BODY',

command||' - ???') COMMAND,

decode(L.LMODE,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive','NONE') lmode,

decode(L.REQUEST,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive','NONE') request,

l.id1||'-'||l.id2 Laddr,

l.type||' - '||

decode(l.type,

'BL','Buffer hash table instance lock',

'CF',' Control file schema global enqueue lock',

'CI','Cross-instance function invocation instance lock',

'CS','Control file schema global enqueue lock',

'CU','Cursor bind lock',

'DF','Data file instance lock',

'DL','Direct loader parallel index create',

'DM','Mount/startup db primary/secondary instance lock',

'DR','Distributed recovery process lock',

'DX','Distributed transaction entry lock',

'FI','SGA open-file information lock',

'FS','File set lock',

'HW','Space management operations on a specific segment lock',

'IN','Instance number lock',

'IR','Instance recovery serialization global enqueue lock',

'IS','Instance state lock',

'IV','Library cache invalidation instance lock',

'JQ','Job queue lock',

'KK','Thread kick lock',

'MB','Master buffer hash table instance lock',

'MM','Mount definition gloabal enqueue lock',

'MR','Media recovery lock',

'PF','Password file lock',

'PI','Parallel operation lock',

'PR','Process startup lock',

'PS','Parallel operation lock',

'RE','USE_ROW_ENQUEUE enforcement lock',

'RT','Redo thread global enqueue lock',

'RW','Row wait enqueue lock',

'SC','System commit number instance lock',

'SH','System commit number high water mark enqueue lock',

'SM','SMON lock',

'SN','Sequence number instance lock',

'SQ','Sequence number enqueue lock',

'SS','Sort segment lock',

'ST','Space transaction enqueue lock',

'SV','Sequence number value lock',

'TA','Generic enqueue lock',

'TD','DDL enqueue lock',

'TE','Extend-segment enqueue lock',

'TM','DML enqueue lock',

'TO','Temporary Table Object Enqueue',

'TT','Temporary table enqueue lock',

'TX','Transaction enqueue lock',

'UL','User supplied lock',

'UN','User name lock',

'US','Undo segment DDL lock',

'WL','Being-written redo log instance lock',

'WS','Write-atomic-log-switch global enqueue lock',

'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',

'New block allocation enqueue lock (ID2=1)'),

'LA','Library cache lock instance lock (A=namespace)',

'LB','Library cache lock instance lock (B=namespace)',

'LC','Library cache lock instance lock (C=namespace)',

'LD','Library cache lock instance lock (D=namespace)',

'LE','Library cache lock instance lock (E=namespace)',

'LF','Library cache lock instance lock (F=namespace)',

'LG','Library cache lock instance lock (G=namespace)',

'LH','Library cache lock instance lock (H=namespace)',

'LI','Library cache lock instance lock (I=namespace)',

'LJ','Library cache lock instance lock (J=namespace)',

'LK','Library cache lock instance lock (K=namespace)',

'LL','Library cache lock instance lock (L=namespace)',

'LM','Library cache lock instance lock (M=namespace)',

'LN','Library cache lock instance lock (N=namespace)',

'LO','Library cache lock instance lock (O=namespace)',

'LP','Library cache lock instance lock (P=namespace)',

'LS','Log start/log switch enqueue lock',

'PA','Library cache pin instance lock (A=namespace)',

'PB','Library cache pin instance lock (B=namespace)',

'PC','Library cache pin instance lock (C=namespace)',

'PD','Library cache pin instance lock (D=namespace)',

'PE','Library cache pin instance lock (E=namespace)',

'PF','Library cache pin instance lock (F=namespace)',

'PG','Library cache pin instance lock (G=namespace)',

'PH','Library cache pin instance lock (H=namespace)',

'PI','Library cache pin instance lock (I=namespace)',

'PJ','Library cache pin instance lock (J=namespace)',

'PL','Library cache pin instance lock (K=namespace)',

'PK','Library cache pin instance lock (L=namespace)',

'PM','Library cache pin instance lock (M=namespace)',

'PN','Library cache pin instance lock (N=namespace)',

'PO','Library cache pin instance lock (O=namespace)',

'PP','Library cache pin instance lock (P=namespace)',

'PQ','Library cache pin instance lock (Q=namespace)',

'PR','Library cache pin instance lock (R=namespace)',

'PS','Library cache pin instance lock (S=namespace)',

'PT','Library cache pin instance lock (T=namespace)',

'PU','Library cache pin instance lock (U=namespace)',

'PV','Library cache pin instance lock (V=namespace)',

'PW','Library cache pin instance lock (W=namespace)',

'PX','Library cache pin instance lock (X=namespace)',

'PY','Library cache pin instance lock (Y=namespace)',

'PZ','Library cache pin instance lock (Z=namespace)',

'QA','Row cache instance lock (A=cache)',

'QB','Row cache instance lock (B=cache)',

'QC','Row cache instance lock (C=cache)',

'QD','Row cache instance lock (D=cache)',

'QE','Row cache instance lock (E=cache)',

'QF','Row cache instance lock (F=cache)',

'QG','Row cache instance lock (G=cache)',

'QH','Row cache instance lock (H=cache)',

'QI','Row cache instance lock (I=cache)',

'QJ','Row cache instance lock (J=cache)',

'QL','Row cache instance lock (K=cache)',

'QK','Row cache instance lock (L=cache)',

'QM','Row cache instance lock (M=cache)',

'QN','Row cache instance lock (N=cache)',

'QO','Row cache instance lock (O=cache)',

'QP','Row cache instance lock (P=cache)',

'QQ','Row cache instance lock (Q=cache)',

'QR','Row cache instance lock (R=cache)',

'QS','Row cache instance lock (S=cache)',

'QT','Row cache instance lock (T=cache)',

'QU','Row cache instance lock (U=cache)',

'QV','Row cache instance lock (V=cache)',

'QW','Row cache instance lock (W=cache)',

'QX','Row cache instance lock (X=cache)',

'QY','Row cache instance lock (Y=cache)',

'QZ','Row cache instance lock (Z=cache)','????') Lockt,

sql_id

from GV$LOCK L,GV$SESSION S,SYS.USER$ U1,SYS.OBJ$ T1

where L.SID = S.SID

and T1.OBJ# = decode(L.ID2,0,L.ID1,1)

and U1.USER# = T1.OWNER#

and S.TYPE != 'BACKGROUND'

order by 1,2,5

Kill String Username Term Table Name COMMAND Lock Held Lock Requested ID1 - ID2 Lock Type SQL_ID

'679,28883,@1' SYS pts/0 None SELECT Share NONE 133-1 AE - ???? 15c8w9f23x0vj

====

--with Con_id

set lines 300 pagesize 66

break on Kill on sid on username on terminal

col Kill heading 'Kill String' format a13

col res heading 'Resource Type' format 999

col id1 format 9999990

col id2 format 9999990

col locking heading 'Lock Held/Lock Requested' format a40

col lmode heading 'Lock Held' format a20

col request heading 'Lock Requested' format a20

col serial# format 99999

col username format a10 heading "Username"

col terminal heading Term format a10

col tab format a30 heading "Table Name"

col owner format a9

col LAddr heading "ID1 - ID2" format a18

col Lockt heading "Lock Type" format a40

col command format a25

col sid format 990

col kill for a16

select

''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,

s.con_id,

nvl(S.USERNAME,'Internal') username,

--L.SID,

nvl(S.TERMINAL,'None') terminal,

decode(command,

0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,

decode(command,

0,'BACKGROUND',

1,'Create Table',

2,'INSERT',

3,'SELECT',

4,'CREATE CLUSTER',

5,'ALTER CLUSTER',

6,'UPDATE',

7,'DELETE',

8,'DROP',

9,'CREATE INDEX',

10,'DROP INDEX',

11,'ALTER INDEX',

12,'DROP TABLE',

13,'CREATE SEQUENCE',

14,'ALTER SEQUENCE',

15,'ALTER TABLE',

16,'DROP SEQUENCE',

17,'GRANT',

18,'REVOKE',

19,'CREATE SYNONYM',

20,'DROP SYNONYM',

21,'CREATE VIEW',

22,'DROP VIEW',

23,'VALIDATE INDEX',

24,'CREATE PROCEDURE',

25,'ALTER PROCEDURE',

26,'LOCK TABLE',

27,'NO OPERATION',

28,'RENAME',

29,'COMMENT',

30,'AUDIT',

31,'NOAUDIT',

32,'CREATE EXTERNAL DATABASE',

33,'DROP EXTERNAL DATABASE',

34,'CREATE DATABASE',

35,'ALTER DATABASE',

36,'CREATE ROLLBACK SEGMENT',

37,'ALTER ROLLBACK SEGMENT',

38,'DROP ROLLBACK SEGMENT',

39,'CREATE TABLESPACE',

40,'ALTER TABLESPACE',

41,'DROP TABLESPACE',

42,'ALTER SESSION',

43,'ALTER USER',

44,'COMMIT',

45,'ROLLBACK',

46,'SAVEPOINT',

47,'PL/SQL EXECUTE',

48,'SET TRANSACTION',

49,'ALTER SYSTEM SWITCH LOG',

50,'EXPLAIN',

51,'CREATE USER',

52,'CREATE ROLE',

53,'DROP USER',

54,'DROP ROLE',

55,'SET ROLE',

56,'CREATE SCHEMA',

57,'CREATE CONTROL FILE',

58,'ALTER TRACING',

59,'CREATE TRIGGER',

60,'ALTER TRIGGER',

61,'DROP TRIGGER',

62,'ANALYZE TABLE',

63,'ANALYZE INDEX',

64,'ANALYZE CLUSTER',

65,'CREATE PROFILE',

66,'DROP PROFILE',

67,'ALTER PROFILE',

68,'DROP PROCEDURE',

69,'DROP PROCEDURE',

70,'ALTER RESOURCE COST',

71,'CREATE SNAPSHOT LOG',

72,'ALTER SNAPSHOT LOG',

73,'DROP SNAPSHOT LOG',

74,'CREATE SNAPSHOT',

75,'ALTER SNAPSHOT',

76,'DROP SNAPSHOT',

79,'ALTER ROLE',

85,'TRUNCATE TABLE',

86,'TRUNCATE CLUSTER',

87,'-',

88,'ALTER VIEW',

89,'-',

90,'-',

91,'CREATE FUNCTION',

92,'ALTER FUNCTION',

93,'DROP FUNCTION',

94,'CREATE PACKAGE',

95,'ALTER PACKAGE',

96,'DROP PACKAGE',

97,'CREATE PACKAGE BODY',

98,'ALTER PACKAGE BODY',

99,'DROP PACKAGE BODY',

command||' - ???') COMMAND,

decode(L.LMODE,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive','NONE') lmode,

decode(L.REQUEST,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Share Row Exclusive',

6,'Exclusive','NONE') request,

l.id1||'-'||l.id2 Laddr,

l.type||' - '||

decode(l.type,

'BL','Buffer hash table instance lock',

'CF',' Control file schema global enqueue lock',

'CI','Cross-instance function invocation instance lock',

'CS','Control file schema global enqueue lock',

'CU','Cursor bind lock',

'DF','Data file instance lock',

'DL','Direct loader parallel index create',

'DM','Mount/startup db primary/secondary instance lock',

'DR','Distributed recovery process lock',

'DX','Distributed transaction entry lock',

'FI','SGA open-file information lock',

'FS','File set lock',

'HW','Space management operations on a specific segment lock',

'IN','Instance number lock',

'IR','Instance recovery serialization global enqueue lock',

'IS','Instance state lock',

'IV','Library cache invalidation instance lock',

'JQ','Job queue lock',

'KK','Thread kick lock',

'MB','Master buffer hash table instance lock',

'MM','Mount definition gloabal enqueue lock',

'MR','Media recovery lock',

'PF','Password file lock',

'PI','Parallel operation lock',

'PR','Process startup lock',

'PS','Parallel operation lock',

'RE','USE_ROW_ENQUEUE enforcement lock',

'RT','Redo thread global enqueue lock',

'RW','Row wait enqueue lock',

'SC','System commit number instance lock',

'SH','System commit number high water mark enqueue lock',

'SM','SMON lock',

'SN','Sequence number instance lock',

'SQ','Sequence number enqueue lock',

'SS','Sort segment lock',

'ST','Space transaction enqueue lock',

'SV','Sequence number value lock',

'TA','Generic enqueue lock',

'TD','DDL enqueue lock',

'TE','Extend-segment enqueue lock',

'TM','DML enqueue lock',

'TO','Temporary Table Object Enqueue',

'TT','Temporary table enqueue lock',

'TX','Transaction enqueue lock',

'UL','User supplied lock',

'UN','User name lock',

'US','Undo segment DDL lock',

'WL','Being-written redo log instance lock',

'WS','Write-atomic-log-switch global enqueue lock',

'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',

'New block allocation enqueue lock (ID2=1)'),

'LA','Library cache lock instance lock (A=namespace)',

'LB','Library cache lock instance lock (B=namespace)',

'LC','Library cache lock instance lock (C=namespace)',

'LD','Library cache lock instance lock (D=namespace)',

'LE','Library cache lock instance lock (E=namespace)',

'LF','Library cache lock instance lock (F=namespace)',

'LG','Library cache lock instance lock (G=namespace)',

'LH','Library cache lock instance lock (H=namespace)',

'LI','Library cache lock instance lock (I=namespace)',

'LJ','Library cache lock instance lock (J=namespace)',

'LK','Library cache lock instance lock (K=namespace)',

'LL','Library cache lock instance lock (L=namespace)',

'LM','Library cache lock instance lock (M=namespace)',

'LN','Library cache lock instance lock (N=namespace)',

'LO','Library cache lock instance lock (O=namespace)',

'LP','Library cache lock instance lock (P=namespace)',

'LS','Log start/log switch enqueue lock',

'PA','Library cache pin instance lock (A=namespace)',

'PB','Library cache pin instance lock (B=namespace)',

'PC','Library cache pin instance lock (C=namespace)',

'PD','Library cache pin instance lock (D=namespace)',

'PE','Library cache pin instance lock (E=namespace)',

'PF','Library cache pin instance lock (F=namespace)',

'PG','Library cache pin instance lock (G=namespace)',

'PH','Library cache pin instance lock (H=namespace)',

'PI','Library cache pin instance lock (I=namespace)',

'PJ','Library cache pin instance lock (J=namespace)',

'PL','Library cache pin instance lock (K=namespace)',

'PK','Library cache pin instance lock (L=namespace)',

'PM','Library cache pin instance lock (M=namespace)',

'PN','Library cache pin instance lock (N=namespace)',

'PO','Library cache pin instance lock (O=namespace)',

'PP','Library cache pin instance lock (P=namespace)',

'PQ','Library cache pin instance lock (Q=namespace)',

'PR','Library cache pin instance lock (R=namespace)',

'PS','Library cache pin instance lock (S=namespace)',

'PT','Library cache pin instance lock (T=namespace)',

'PU','Library cache pin instance lock (U=namespace)',

'PV','Library cache pin instance lock (V=namespace)',

'PW','Library cache pin instance lock (W=namespace)',

'PX','Library cache pin instance lock (X=namespace)',

'PY','Library cache pin instance lock (Y=namespace)',

'PZ','Library cache pin instance lock (Z=namespace)',

'QA','Row cache instance lock (A=cache)',

'QB','Row cache instance lock (B=cache)',

'QC','Row cache instance lock (C=cache)',

'QD','Row cache instance lock (D=cache)',

'QE','Row cache instance lock (E=cache)',

'QF','Row cache instance lock (F=cache)',

'QG','Row cache instance lock (G=cache)',

'QH','Row cache instance lock (H=cache)',

'QI','Row cache instance lock (I=cache)',

'QJ','Row cache instance lock (J=cache)',

'QL','Row cache instance lock (K=cache)',

'QK','Row cache instance lock (L=cache)',

'QM','Row cache instance lock (M=cache)',

'QN','Row cache instance lock (N=cache)',

'QO','Row cache instance lock (O=cache)',

'QP','Row cache instance lock (P=cache)',

'QQ','Row cache instance lock (Q=cache)',

'QR','Row cache instance lock (R=cache)',

'QS','Row cache instance lock (S=cache)',

'QT','Row cache instance lock (T=cache)',

'QU','Row cache instance lock (U=cache)',

'QV','Row cache instance lock (V=cache)',

'QW','Row cache instance lock (W=cache)',

'QX','Row cache instance lock (X=cache)',

'QY','Row cache instance lock (Y=cache)',

'QZ','Row cache instance lock (Z=cache)','????') Lockt,

sql_id,l.request lrequest

from gv$lock l,gv$session s,sys.user$ u1,sys.obj$ t1

where l.sid = s.sid

and t1.obj# = decode(l.id2,0,l.id1,1)

and u1.user# = t1.owner#

and S.TYPE != 'BACKGROUND'

order by 1,2,5

set linesize 500 pagesize 300
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
col kill for a17
col USERNAME for a20
select /*+ ordered */
--b.kaddr,
--c.sid, c.inst_id,
''''||c.sid ||','|| c.serial#||',@'||c.inst_id ||'''' kill,
c.con_id,
c.USERNAME,
c.sql_id,
c.prev_sql_id ,
lock_waiter.waiting_session,
lock_blocker.holding_session,
c.program,
c.osuser,
c.machine,
c.process,
decode(u.name,
null,'',
u.name||'.'||o.name
) object,
--c.username,
decode
(
b.type,
'BL', 'Buffer hash table instance lock',
'CF', 'Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'direct loader parallel index create lock',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FS', 'File set lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'LA','Library cache lock instance lock (A..P=namespace);',
'LB','Library cache lock instance lock (A..P=namespace);',
'LC','Library cache lock instance lock (A..P=namespace);',
'LD','Library cache lock instance lock (A..P=namespace);',
'LE','Library cache lock instance lock (A..P=namespace);',
'LF','Library cache lock instance lock (A..P=namespace);',
'LG','Library cache lock instance lock (A..P=namespace);',
'LH','Library cache lock instance lock (A..P=namespace);',
'LI','Library cache lock instance lock (A..P=namespace);',
'LJ','Library cache lock instance lock (A..P=namespace);',
'LK','Library cache lock instance lock (A..P=namespace);',
'LL','Library cache lock instance lock (A..P=namespace);',
'LM','Library cache lock instance lock (A..P=namespace);',
'LN','Library cache lock instance lock (A..P=namespace);',
'LO','Library cache lock instance lock (A..P=namespace);',
'LP','Library cache lock instance lock (A..P=namespace);',
'MM', 'Mount definition global enqueue lock',
'MR', 'Media recovery lock',
'NA', 'Library cache pin instance lock (A..Z=namespace)',
'NB', 'Library cache pin instance lock (A..Z=namespace)',
'NC', 'Library cache pin instance lock (A..Z=namespace)',
'ND', 'Library cache pin instance lock (A..Z=namespace)',
'NE', 'Library cache pin instance lock (A..Z=namespace)',
'NF', 'Library cache pin instance lock (A..Z=namespace)',
'NG', 'Library cache pin instance lock (A..Z=namespace)',
'NH', 'Library cache pin instance lock (A..Z=namespace)',
'NI', 'Library cache pin instance lock (A..Z=namespace)',
'NJ', 'Library cache pin instance lock (A..Z=namespace)',
'NK', 'Library cache pin instance lock (A..Z=namespace)',
'NL', 'Library cache pin instance lock (A..Z=namespace)',
'NM', 'Library cache pin instance lock (A..Z=namespace)',
'NN', 'Library cache pin instance lock (A..Z=namespace)',
'NO', 'Library cache pin instance lock (A..Z=namespace)',
'NP', 'Library cache pin instance lock (A..Z=namespace)',
'NQ', 'Library cache pin instance lock (A..Z=namespace)',
'NR', 'Library cache pin instance lock (A..Z=namespace)',
'NS', 'Library cache pin instance lock (A..Z=namespace)',
'NT', 'Library cache pin instance lock (A..Z=namespace)',
'NU', 'Library cache pin instance lock (A..Z=namespace)',
'NV', 'Library cache pin instance lock (A..Z=namespace)',
'NW', 'Library cache pin instance lock (A..Z=namespace)',
'NX', 'Library cache pin instance lock (A..Z=namespace)',
'NY', 'Library cache pin instance lock (A..Z=namespace)',
'NZ', 'Library cache pin instance lock (A..Z=namespace)',
'PF', 'Password File lock',
'PI', 'Parallel operation locks',
'PS', 'Parallel operation locks',
'PR', 'Process startup lock',
'QA','Row cache instance lock (A..Z=cache)',
'QB','Row cache instance lock (A..Z=cache)',
'QC','Row cache instance lock (A..Z=cache)',
'QD','Row cache instance lock (A..Z=cache)',
'QE','Row cache instance lock (A..Z=cache)',
'QF','Row cache instance lock (A..Z=cache)',
'QG','Row cache instance lock (A..Z=cache)',
'QH','Row cache instance lock (A..Z=cache)',
'QI','Row cache instance lock (A..Z=cache)',
'QJ','Row cache instance lock (A..Z=cache)',
'QK','Row cache instance lock (A..Z=cache)',
'QL','Row cache instance lock (A..Z=cache)',
'QM','Row cache instance lock (A..Z=cache)',
'QN','Row cache instance lock (A..Z=cache)',
'QP','Row cache instance lock (A..Z=cache)',
'QQ','Row cache instance lock (A..Z=cache)',
'QR','Row cache instance lock (A..Z=cache)',
'QS','Row cache instance lock (A..Z=cache)',
'QT','Row cache instance lock (A..Z=cache)',
'QU','Row cache instance lock (A..Z=cache)',
'QV','Row cache instance lock (A..Z=cache)',
'QW','Row cache instance lock (A..Z=cache)',
'QX','Row cache instance lock (A..Z=cache)',
'QY','Row cache instance lock (A..Z=cache)',
'QZ','Row cache instance lock (A..Z=cache)',
'RT', 'Redo thread global enqueue lock',
'SC', 'System commit number instance lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment locks',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TS', 'Temporary segment enqueue lock (ID2=0)',
'TS', 'New block allocation enqueue lock (ID2=1)',
'TT', 'Temporary table enqueue lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
b.type
) lock_type,
decode
(
b.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SRX)', /* C */
6, 'Exclusive', /* X */
to_char(b.lmode)
) mode_held,
decode
(
b.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(b.request)
) mode_requested,
c.event
from
GV$lock b
,GV$session c
,sys.user$ u
,sys.obj$ o
,( select * from sys.dba_waiters) lock_blocker
,( select * from sys.dba_waiters) lock_waiter
where
b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by kaddr, lockwait
/

====

to check event

from Web ....

define V_EVENTNAME='enq: TX - row lock contention'
set linesize 300
clear breaks computes
ttitle center 'Hourly trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 9,990.00 heading "Waits (m)"
break on day skip 1 on hr on report
compute avg of total_waits on report
compute avg of time_waited on report
compute avg of avg_wait on report
prompt
select sort_hr || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
day,
hr,
event_name,
total_waits/1000000 total_waits,
(ratio_to_report(total_waits) over (partition by day)*100) tot_wts,
rpad('*', round((ratio_to_report(total_waits) over (partition by day)*100)/4, 0), '*') wt_graph,
time_waited,
(ratio_to_report(time_waited) over (partition by day)*100) tot_pct,
rpad('*', round((ratio_to_report(time_waited) over (partition by day)*100)/4, 0), '*') tot_graph,
avg_wait*100 avg_wait,
(ratio_to_report(avg_wait) over (partition by day)*100) avg_pct,
rpad('*', round((ratio_to_report(avg_wait) over (partition by day)*100)/4, 0), '*') avg_graph
from (select sort_hr,
day,
hr,
event_name,
sum(total_waits) total_waits,
sum(time_waited)/1000000 time_waited,
decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
from (select to_char(ss.begin_interval_time, 'YYYYMMDDHH24') sort_hr,
to_char(ss.begin_interval_time, 'DD-MON') day,
to_char(ss.begin_interval_time, 'HH24')||':00' hr,
s.event_name,
s.snap_id,
nvl(decode(greatest(s.time_waited_micro,
lag(s.time_waited_micro,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.time_waited_micro,
s.time_waited_micro - lag(s.time_waited_micro)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.time_waited_micro), 0) time_waited,
nvl(decode(greatest(s.total_waits,
lag(s.total_waits,1,0)
over (partition by s.dbid,
s.instance_number
order by s.snap_id)),
s.total_waits,
s.total_waits - lag(s.total_waits)
over (partition by s.dbid,
s.instance_number
order by s.snap_id),
s.total_waits), 0) total_waits
from dba_hist_system_event s,
dba_hist_snapshot ss
where s.event_name like '%'||'&&V_EVENTNAME'||'%'
-- and s.instance_number = &&V_INST_NBR
and s.dbid = &&V_DBID
and ss.snap_id = s.snap_id
and ss.dbid = s.dbid
and ss.instance_number = s.instance_number
and ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
group by sort_hr,
day,
hr,
event_name)
order by sort0;

====

set lines 1000 pages 1000 feed on verify off echo off

column db_username heading 'DB|Username' format a15 wrap

column process heading 'Process' format a10 truncate

column host_pid heading 'Host|PID' format a8 wrap

column machine heading 'Host' format a12 truncate

column program heading 'Program' format a25 truncate

column object_name heading 'Object|Name' format a20 wrap

column lock_type heading 'Lock|Type' format a5 truncate

column mode_held heading 'Mode|Held' format a10 truncate

column mode_req heading 'Mode|Requested' format a15 truncate

column ctime heading 'Time Hrs.|Since|LMode|Held' format a8 truncate

column is_blocking heading 'Blocking?' format a12 wrap

col kill for a15

break on sid on serial_num on process on db_username on spid on machine

select /*+rule */

''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill,

ses.process as process,

ses.username as db_username,

pro.spid as host_pid,

ses.machine as machine,

substr(ses.program,1,30) as program,

substr(obj.object_name,1,20) as object_name,

loc.lock_type as lock_type,

loc.mode_held as mode_held,

loc.mode_requested as mode_req,

to_char(trunc(sysdate) + loc.last_convert/(24*3600), 'hh24:mi:ss') as ctime,

loc.blocking_others as is_blocking,

ses.sql_id,

ses.prev_sql_id,

ses.event,

final_blocking_session,

final_blocking_session_status

from gv$session ses,gv$process pro,dba_lock loc,dba_objects obj

where ses.sid = loc.session_id

and ses.paddr = pro.addr

and loc.lock_id1 = obj.object_id

and ses.username is not null

and ses.inst_id=pro.inst_id

and ses.paddr=pro.addr

and obj.object_name not in ('ORA$BASE')

order by ses.sid, ses.serial#, ses.process, ses.username

clear columns

clear breaks

=======

set linesize 300

col OBJECT for a35

col kill for a16

col ORA_USER for a15

col EVENT for a25

SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,15) ORA_USER, S.serial#,SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,

DECODE(L.LOCKED_MODE, 0,'NONE',

1,'NULL',

2,'ROW SHARE',

3,'ROW EXCLUSIVE',

4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE',

NULL) LOCK_MODE,sql_id,PREV_SQL_ID,event,

final_blocking_session,

final_blocking_session_status

FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

and l.inst_id = s.inst_id

AND L.SESSION_ID = S.SID

and s.inst_id = p.inst_id

AND S.PADDR = P.ADDR(+)

order by l.inst_id ;

====

set linesize 300 pagesize 300

col kill for a17

col USERNAME for a20

col SQL_TEXT for a50 wrap

col EVENT for a20

select ''''||Sn.sid ||','|| Sn.serial#||',@'||Sn.inst_id ||'''' kill,

sn.con_id,

sn.USERNAME,

--m.SID,

--sn.SERIAL#,

m.TYPE,

decode(LMODE,

0, 'None',

1, 'Null',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share',

5, 'S/Row-X (SSX)',

6, 'Exclusive') lock_type,

decode(REQUEST,

0, 'None',

1, 'Null',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share',

5, 'S/Row-X (SSX)',

6, 'Exclusive') lock_requested,

m.ID1,

m.ID2,

sn.event,

t.SQL_TEXT

from gv$session sn,gv$lock m ,gv$sqltext t

where t.ADDRESS = sn.SQL_ADDRESS

and t.hash_value = sn.sql_hash_value

and t.inst_id = sn.inst_id

and ((sn.SID = m.SID and m.REQUEST != 0 and sn.inst_id = m.inst_id) or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in (select s.ID1, s.ID2 from gv$lock S

where REQUEST != 0

and s.ID1 = m.ID1

and s.ID2 = m.ID2)))

order by sn.USERNAME, sn.SID, t.PIECE;

KILL CON_ID USERNAME TY LOCK_TYPE LOCK_REQUESTE ID1 ID2 EVENT SQL_TEXT

----------------- ---------- -------------------- -- ------------- ------------- ---------- ---------- -------------------- --------------------------------------------------

'1709,51066,@1' 0 SYS TM None Row-X (SX) 147631 0 enq: TM - contention update scott.emp set comm=100

======

set pages 1000 lines 500

select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot

where 1=1

and begin_interval_time>sysdate -2

--and to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2021'

--and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23

order by 1 desc ;

https://github.com/iusoltsev/sqlplus/blob/master/ash_sql_lock_hist.sql

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col INST_ID for 9999999

col WAIT_LEVEL for 999

col BLOCKING_TREE for a30

col EVENT for a40

col SQL_TEXT for a100

col MODULE for a50

col CLIENT_ID for a40

col WAITS for 999999

col AVG_WAIT_TIME_MS for 999999

col DATA_OBJECT for a52

col BLOCKED_CLIENT_ID for a15

col CURRENT_OBJ#1 for a15

col BLOCKING_WAIT for a15

col CURRENT_OBJ#2 for a15

col WAITED_FOR for a35

col INST_ID for 9999999

col WAIT_LEVEL for 999

col BLOCKING_TREE for a30

col EVENT for a25

col SQL_TEXT for a100

col MODULE for a30

col CLIENT_ID for a25

col WAITS for 999999

col AVG_WAIT_TIME_MS for 999999

col DATA_OBJECT for a20

col BLOCKING_CLIENT_ID for a50

col CLIENT_ID for a50

with ash_locks as

(select snap_id,

instance_number as BLOCKED_INST_ID,

session_id as BLOCKED_SID,

session_serial# as BLOCKED_SERIAL#,

nvl(REGEXP_SUBSTR(client_id, '.+\#'),

case when REGEXP_INSTR(program, '$[A-Z]...$') = 0 then program

when REGEXP_INSTR(program, '$ARC.$') > 0 then '(ARC.)'

when REGEXP_INSTR(program, '$O...$') > 0 then '(O...)'

when REGEXP_INSTR(program, '$P...$') > 0 then '(P...)'

else REGEXP_REPLACE(REGEXP_SUBSTR(program, '$[^$]+\)'), '([[:digit:]])', '.')

end) as BLOCKED_CLIENT_ID,

sql_id as BLOCKED_SQL_ID,

CURRENT_OBJ# as BLOCKED_OBJ#,

BLOCKING_INST_ID,

BLOCKING_SESSION,

BLOCKING_SESSION_SERIAL#,

event,

sample_time

from dba_hist_active_sess_history

-- where &1 -- event = 'enq: TX - row lock contention' and snap_id = 325800

where 1=1

and event = 'enq: TX - row lock contention'

-- and snap_id = 325800

and BLOCKING_SESSION > 0

)

, ash_lockers as

(select * from

(select ash_locks.sample_time,

ash_locks.BLOCKED_INST_ID,

ash_locks.BLOCKED_SID,

ash_locks.BLOCKED_SERIAL#,

ash_locker.snap_id,

ash_locker.client_id as BLOCKING_CLIENT_ID,

ash_locker.sql_id as BLOCKING_SQL_ID,

ash_locker.session_id as BLOCKING_SID,

ash_locker.session_serial# as BLOCKING_SERIAL#,

ash_locker.instance_number as BLOCKING_INST_ID,

ash_locker.sample_time - ash_locks.sample_time as BLOCK_TIME_LAG,

ash_locker.sample_time as BLOCKING_TIME,

ash_locker.CURRENT_OBJ# as BLOCKING_OBJ#,

nvl2(ash_locker.XID,'*','') as XID,

decode(ash_locker.Session_State , 'WAITING', ash_locker.event, ash_locker.Session_State) as BLOCKING_WAIT,

ash_locker.program,

ash_locker.module,

ash_locker.action,

rank() over ( partition by ash_locks.sample_time,

ash_locks.BLOCKED_INST_ID,

ash_locks.BLOCKED_SID,

ash_locks.BLOCKED_SERIAL#

order by abs(cast(ash_locks.sample_time as date) - cast(ash_locker.sample_time as date)) asc ) as rnk

from dba_hist_active_sess_history ash_locker

join ash_locks

on ash_locker.snap_id = ash_locks.snap_id

and ash_locker.session_id = ash_locks.BLOCKING_SESSION

and ash_locker.session_serial# = ash_locks.BLOCKING_SESSION_SERIAL#

and ash_locker.instance_number = ash_locks.BLOCKING_INST_ID

-- and ash_locker.sample_time <= ash_locks.sample_time

)

where rnk = 1)

select BLOCKED_INST_ID as INST#1,

BLOCKED_CLIENT_ID,

BLOCKED_SQL_ID,

do1.owner || '.' || do1.object_name as CURRENT_OBJ#1,

EVENT as WAITED_FOR,

count(distinct BLOCKED_INST_ID||' '||

BLOCKED_SID||' '||

BLOCKED_SERIAL#) as BLOCKED_SIDS,

count(*) as WAIT_COUNT,

to_char(min(sample_time),'DD.MM HH24:MI:SS') as MIN_WAITS_TIME,

to_char(max(sample_time),'DD.MM HH24:MI:SS') as MAX_WAITS_TIME,

ash_lockers.BLOCKING_INST_ID as INST#2,

nvl(REGEXP_SUBSTR(BLOCKING_CLIENT_ID, '.+\#'),

case when REGEXP_INSTR(program, '$[A-Z]...$') = 0 then program

when REGEXP_INSTR(program, '$ARC.$') > 0 then '(ARC.)'

when REGEXP_INSTR(program, '$O...$') > 0 then '(O...)'

when REGEXP_INSTR(program, '$P...$') > 0 then '(P...)'

else REGEXP_REPLACE(REGEXP_SUBSTR(program, '$[^$]+\)'), '([[:digit:]])', '.')

end) as BLOCKING_CLIENT_ID,

-- REGEXP_SUBSTR(BLOCKING_CLIENT_ID, '.+\#') as BLOCKING_CLIENT_ID,

-- program,

module,

action,

BLOCKING_SQL_ID,

BLOCKING_WAIT,

do2.owner || '.' || do2.object_name as CURRENT_OBJ#2,

XID,

cast((min(BLOCK_TIME_LAG))AS INTERVAL DAY(1) TO SECOND(0)) as LOCK_TIME_LAG

--, min(BLOCKING_TIME)

--, max(BLOCKING_TIME)

from ash_locks

left join ash_lockers using (BLOCKED_INST_ID, BLOCKED_SID, BLOCKED_SERIAL#, SAMPLE_TIME, SNAP_ID)

left join dba_objects do1 on do1.object_id = BLOCKED_OBJ#

left join dba_objects do2 on do2.object_id = BLOCKING_OBJ#

group by BLOCKED_INST_ID,

BLOCKED_CLIENT_ID,

BLOCKED_SQL_ID,

do1.owner || '.' || do1.object_name,

EVENT,

ash_lockers.BLOCKING_INST_ID,

-- REGEXP_SUBSTR(BLOCKING_CLIENT_ID, '.+\#'),

-- program,

nvl(REGEXP_SUBSTR(BLOCKING_CLIENT_ID, '.+\#'),

case when REGEXP_INSTR(program, '$[A-Z]...$') = 0 then program

when REGEXP_INSTR(program, '$ARC.$') > 0 then '(ARC.)'

when REGEXP_INSTR(program, '$O...$') > 0 then '(O...)'

when REGEXP_INSTR(program, '$P...$') > 0 then '(P...)'

else REGEXP_REPLACE(REGEXP_SUBSTR(program, '$[^$]+\)'), '([[:digit:]])', '.')

end),

module,

action,

BLOCKING_SQL_ID,

BLOCKING_WAIT,

do2.owner || '.' || do2.object_name,

XID

having count(*) >= nvl('&2',0)

order by count(*) desc

INST#1 BLOCKED_CLIENT_ID BLOCKED_SQL_I CURRENT_OBJ#1 WAITED_FOR BLOCKED_SIDS WAIT_COUNT MIN_WAITS_TIME MAX_WAITS_TIME INST#2 BLOCKING_CLIENT_ID MODULE ACTION BLOCKING_SQL_ BLOCKING_WAIT CURRENT_OBJ#2 X LOCK_TIME_LAG

---------- -------------------------------------------------- ------------- --------------- ----------------------------------- ------------ ---------- -------------- -------------- ---------- -------------------------------------------------- ------------------------------ ---------------------------------------------------------------- ------------- --------------- --------------- - ---------------------------------------------------------------------------

1 sqlplus@************************** (TNS V1-V3) frdp5usxnk8g4 SCOTT.EMP enq: TX - row lock contention 1 784 24.02 09:28:18 24.02 11:38:55 .

1 row selected.

========

https://github.com/iusoltsev/sqlplus/blob/master/lock_tree2.sql

set linesize 700 pagesize 300

col BLOCKING_TREE for a20

col USER_CLIENT for a20

col OSUSER for a20

col KILL_SESSION for a40

col P1TEXT for a15

col P2TEXT for a15

col REQ_OBJECT for a15

col SQL_TEXT for a50 wrap

col EVENT for a20

with

LOCK_SESS as (select /*+ MATERIALIZE ordered */ * from gv$lock l join gv$session s using (inst_id, sid) where block > 0 OR request > 0)

--select * from LOCK_SESS -- QCSJ_C000000000300003

,LOCKS as (select distinct inst_id, sid, addr, kaddr, QCSJ_C000000000300000 as type, id1, id2, lmode, request, block, QCSJ_C000000000300002 as con_id from LOCK_SESS)

,S as (select distinct s.inst_id, s.sid, serial#, QCSJ_C000000000300003 as con_id, program, USERNAME, CLIENT_IDENTIFIER, EVENT, last_call_et, seconds_in_wait, blocking_session_status, pdml_enabled, sql_id, prev_sql_id,

osuser, machine, process, port, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, p1, p1raw, p1text, p2, p2raw, p2text, p3, p3raw, p3text, paddr, LOCKWAIT,

-- blocking_session, blocking_instance

nvl(s.blocking_session, l.sid) as blocking_session,

nvl(s.blocking_instance, l.inst_id) as blocking_instance

from LOCK_SESS s

left join LOCKS l on s.inst_id = l.inst_id and s.sid = l.sid and s.LOCKWAIT = l.KADDR)

--select * from S

,BLOCKERS as

(select distinct L1.inst_id, L1.sid, L1.con_id--, UTL_RAW.CAST_TO_VARCHAR2(L1.KADDR)

from LOCKS L1, LOCKS L2

where L1.block > 0

and L1.ID1 = L2.ID1

and L1.ID2 = L2.ID2

and L2.REQUEST > 0)

--select * from BLOCKERS

,WAITERS as (select inst_id, sid, con_id from S where blocking_session is not null or blocking_instance is not null

union

select distinct L2.inst_id, L2.sid, L2.con_id--, UTL_RAW.CAST_TO_VARCHAR2(L2.KADDR)

from LOCKS L1, LOCKS L2

where L1.block > 0

and L1.ID1 = L2.ID1

and L1.ID2 = L2.ID2

and L2.REQUEST > 0)

--select * from WAITERS

--select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS

--select s.* from s join WAITERS l on s.inst_id = l.inst_id and s.sid = l.sid

select--+ opt_param('_connect_by_use_union_all' 'false')

LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || s.inst_id || ' SID#' || s.sid || ' CON#' || s.con_id as BLOCKING_TREE,

--l.type,

--s.LOCKWAIT,

--l.KADDR,

s.program,

substr(s.USERNAME || ' ' || s.CLIENT_IDENTIFIER,1,60) as USER_CLIENT,

s.EVENT,

object_type || ' ' || owner ||'.'|| object_name req_object,

s.last_call_et,

s.seconds_in_wait as SECS_IN_WAIT,

s.blocking_session_status as BLOCK_SESSTAT,

s.pdml_enabled,

NVL(s.sql_id,s.prev_sql_id) as SQL_ID,

s.osuser,

p.spid,

s.machine,

s.process as CLNT_PID,

s.port as CLNT_PORT,

substr(trim(NVL((select sa1.sql_text from gv$sqlarea sa1 where s.sql_id = sa1.sql_id and rownum < 2),

(select sa2.sql_text from gv$sqlarea sa2 where s.prev_sql_id = sa2.sql_id and rownum < 2))), 1, 100) SQL_TEXT,

decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) as req_rowid,

s.p1text || ' ' || decode(s.p1text, 'name|mode', chr(bitand(s.p1,-16777216)/16777215)||chr(bitand(s.p1, 16711680)/65535)||' '||bitand(s.p1, 65535), s.p1text) as p1text,

s.p1,

s.p1raw,

s.p2text || ' ' || decode(s.p2text, 'object #', o.object_name || ' ' || o.owner || '.' || o.object_name, '') as p2text,

s.p2

, 'Alter system kill session '''||s.SID||','||s.SERIAL#||','||'@'||s.INST_ID||''';' as KILL_SESSION

from s

---- left join LOCKS l on s.inst_id = l.inst_id and s.sid = l.sid and s.LOCKWAIT = l.KADDR--UTL_RAW.CAST_TO_RAW(s.LOCKWAIT) = l.KADDR--

-- left join gv$sqlarea sa1 on s.sql_id = sa1.sql_id and s.inst_id = sa1.inst_id and s.con_id = sa1.con_id

-- left join gv$sqlarea sa2 on s.prev_sql_id = sa2.sql_id and s.inst_id = sa2.inst_id and s.con_id = sa2.con_id

left join cdb_objects o on s.p2 = o.object_id and s.con_id = o.con_id -- here be dragonz

left join gv$process p on s.paddr = p.addr and s.inst_id = p.inst_id and s.con_id = p.con_id

connect by NOCYCLE prior s.sid = s.blocking_session and prior s.inst_id = s.blocking_instance

----l.sid and prior s.inst_id = l.inst_id

start with (s.inst_id, s.sid) in (select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS)

===

--define event='db file scattered read'

define event='enq: TX - row lock contention'

col WAIT_LEVEL for 999

col BLOCKING_TREE for a30

col EVENT for a40

col WAITS for 999999

col AVG_TIME_WAITED_MS for 999999

select LEVEL as WAIT_LEVEL,

LPAD(' ', (LEVEL - 1) * 2) || decode(ash.session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '$[^$]+\)'), 'FOREGROUND') as BLOCKING_TREE,

ash.EVENT,

sql_id,

count(*) as WAITS_COUNT,

round(avg(time_waited) / 1000) as AVG_TIME_WAITED_MS,

round(sum(case when time_waited > 0 then greatest(1, (1000000 / time_waited)) else 0 end)) as est_waits,

round(sum(1000) / round(sum(case when time_waited > 0 then greatest(1, (1000000 / time_waited)) else 1 end))) as est_avg_latency_ms

from gv$active_session_history ash

where session_state = 'WAITING'

start with event = nvl('&event',event)

connect by nocycle prior ash.sample_id = ash.sample_id

and ash.session_id = prior ash.blocking_session

group by LEVEL,LPAD(' ', (LEVEL - 1) * 2) || decode(ash.session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '$[^$]+\)'), 'FOREGROUND'),ash.EVENT,sql_id

order by LEVEL, count(*) desc

set lines 300 pages 300

col BLOCKING_SESSION for a10

col LAST_LOCKED_SESSION for a10

col PATH_EVENT for a120

with

snap as (

select

inst_id

, sid

, blocking_instance

, blocking_session

, sql_id

, prev_sql_id

, event

, state

from gv$session)

select

to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now

, CONNECT_BY_ISCYCLE cycle

, CONNECT_BY_ROOT inst_id ||':'||

CONNECT_BY_ROOT sid blocking_session

, inst_id ||':'||

SID last_locked_session

, SUBSTR(SYS_CONNECT_BY_PATH(

DECODE(state,

'WAITING', sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,

sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')

,5) path_event

from snap

where

level > 1

connect by nocycle prior sid = blocking_session

and prior inst_id = blocking_instance;

select count(*),MAX(sample_id),min(sample_id) FROM gv$active_session_history;

COUNT(*) MAX(SAMPLE_ID) MIN(SAMPLE_ID)

---------- -------------- --------------

709393 123844597 123350646

col HIERARCHY for a30

col PATH for a30

define sample_id = '123844597'

WITH x AS

(

SELECT session_id,con_id, blocking_session, session_state, event,sql_id FROM gv$active_session_history

WHERE sample_id = &sample_id

UNION ALL

SELECT blocking_session,con_id, null, null, 'IDLE?','sql_id' FROM gv$active_session_history

WHERE sample_id = &sample_id

AND blocking_session NOT IN (SELECT session_id

FROM gv$active_session_history

WHERE sample_id = &sample_id)

GROUP BY blocking_session,con_id

)

SELECT

LEVEL,

LPAD(' ', 2 * (LEVEL - 1)) || session_id hierarchy,

SYS_CONNECT_BY_PATH(session_id, '<-') path,

session_id,

con_id,

blocking_session,

session_state,

event,

sql_id

FROM x

START WITH blocking_session IS NULL

CONNECT BY PRIOR session_id = blocking_session

ORDER SIBLINGS BY session_id

LEVEL HIERARCHY PATH SESSION_ID BLOCKING_SESSION SESSION Current/LastEvent

---------- ------------------------------ ------------------------------ ---------- ---------------- ------- ----------------------------------------

1 6671 <-6671 6671 WAITING direct path read

1 13202 <-13202 13202 ON CPU

===

set linesize 400 pagesize 300

COLUMN INST_ID HEADING "I#" FORMAT 99

COLUMN SID FORMAT a27

COLUMN username HEADING "UserName" FORMAT a20 TRUNC

COLUMN lock_type HEADING "Lock|type" FORMAT a4 TRUNC

COLUMN lock_Name HEADING "Lock Name" FORMAT a22 TRUNC

COLUMN osuser HEADING "OSUser" FORMAT a20 TRUNC

COLUMN machine HEADING "Client|Machine" FORMAT a20 TRUNC

COLUMN blocking_instance HEADING "Bl'ing|Inst"

COLUMN blocking_session HEADING "Bl'ing|SID"

COLUMN blocking_session_status HEADING "Bl'ing|Session|Status"

COLUMN final_blocking_instance HEADING "Final|Bl'ing|Inst"

COLUMN final_blocking_session HEADING "Final|Bl'ing|SID"

COLUMN final_blocking_session_status HEADING "Final|Bl'ing|Session|Status"

COLUMN program HEADING "Program" FORMAT a30 TRUNC

COLUMN event HEADING "Event" FORMAT a30 TRUNC

-- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''

WITH sess as

( select /*+ materialize */ s1.* from gv$session s1

)

, locks as

(select /*+ materialize */ * from gv$lock)

SELECT distinct s.inst_id

--,s.con_id

, DECODE(level

,1 , ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || ' (Blocker)'

, ' ' || LPAD('-',(level-1)*4,'|---') || '> ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''

)

-- || NVL2(s2.blocking_session, ' (Blocker)', ' ')

sid

, s.status

-- , l.lmode

-- , l.request

-- , l.block

-- , s.lock_type

, lt.name lock_name

, s.username

, s.osuser

, s.sql_id

, s.program

, s.event

-- , s.blocking_instance

-- , s.blocking_session

-- , s.blocking_session_status

-- , s.final_blocking_instance

-- , s.final_blocking_session

, s.final_blocking_session_status

FROM sess s

LEFT OUTER JOIN locks l ON l.inst_id = s.inst_id

AND l.sid = s.sid AND

--( l.lmode = 0 OR (l.lmode > 0 AND l.block > 0))

l.lmode = 0

LEFT OUTER JOIN v$lock_type lt ON l.type = lt.type

LEFT OUTER JOIN sess s2 ON s.inst_id = s2.blocking_instance AND s.sid = s2.blocking_session

WHERE (

s.blocking_session IS NOT NULL

OR s2.blocking_session IS NOT NULL

)

CONNECT BY PRIOR s.inst_id = s.blocking_instance

AND PRIOR s.sid = s.blocking_session

START WITH s.blocking_session IS NULL

;

Final

Bl'ing

Session

I# SID STATUS Lock Name UserName OSUser SQL_ID Program Event Status

--- --------------------------- -------- ---------------------- -------------------- -------------------- ------------- ------------------------------ ------------------------------ -----------

1 '1225,48073,@1' (Blocker) INACTIVE SYS oracle sqlplus@xxxxxxxxxxxxxxxxatapi SQL*Net message from client UNKNOWN

1 |---> '1469,6107,@1' ACTIVE Transaction SYS oracle brpgwz53q45w6 sqlplus@xxxxxxxxxxxxxxxxatapi enq: TX - row lock contention VALID

1 |---> '1714,24660,@1' ACTIVE Transaction SYS oracle brpgwz53q45w6 sqlplus@xxxxxxxxxxxxxxxxatapi enq: TX - row lock contention VALID

1 |---> '1231,6611,@1' ACTIVE Transaction SYS oracle brpgwz53q45w6 sqlplus@xxxxxxxxxxxxxxxxatapi enq: TX - row lock contention VALID

1 |---> '738,9486,@1' ACTIVE Transaction SYS oracle f2m5yy47v2u60 sqlplus@xxxxxxxxxxxxxxxxatapi enq: TX - row lock contention VALID

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 500 pagesize 300

clear columns

col osuser for a16

col program for a10 trunc

col Locked for a6

col status for a1 trunc print

col "hh:mm:ss" for a8

col SQL_ID for a15

col seq# for 99999 --Sequence number that uniquely identifies the wait. Incremented for each wait

col module for a25

col ACTION for a20

col kill for a15

col event heading 'Current/LastEvent' for a25 trunc

col state head 'State (sec)' for a14

col username for a25

select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill

,con_id --12c

, username

, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser

, substr(program,instr(program,'/',-1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program,

decode(lockwait,NULL,' ','L') locked, status,

to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , sql_id,SQL_CHILD_NUMBER,prev_sql_id, prev_child_number, seq# , event,

decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state

, substr(module,1,25) module,final_blocking_session_status,sql_exec_start

,substr(action,1,20) action

from GV$SESSION

where 1=1

--and type = 'USER'

--and STATUS!='INACTIVE'

--and audsid != 0 -- to exclude internal processess

--and USERNAME='ODS'

--and SQL_ID='1mavr84vthn3s'

--and event not like 'SQL*Net message from%'

-- and sid ||','|| serial#||',@'||inst_id =&Kill -- from above

order by inst_id, status, last_call_et desc, sid

KILL CON_ID USERNAME OSUSER PROGRAM LOCKED S hh:mm:ss SQL_ID PREV_SQL_ID SEQ# Current/LastEvent State (sec) MODULE FINAL_BLOCK SQL_EXEC_START

'1225,48073,@1' 0 SYS oracle sqlplus I 02:12:40 brpgwz53q45w6 92 SQL*Net message from clie WAITING 7960 sqlplus@xxxxxxxxxxxxxxxx UNKNOWN

var sqlid VARCHAR2(13);

begin :sqlid := '36psp2j8kf741'; end; ----- change sql id

set long 50000 pagesize 500 linesize 300

col frm heading from

select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid

union all

select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid

);

set pagesize 300 linesize 300

var sqlid VARCHAR2(13);

var cursorchildno number ;

begin :sqlid := '9zg9qd9bm4spu'; end; ----- change sql id

begin :cursorchildno := 4; end;

set linesize 500 pagesize 300

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'9zg9qd9bm4spu',format=>'ALLSTATS LAST +cost +bytes'));

SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => :sqlid, cursor_child_no => :cursorchildno, FORMAT => 'TYPICAL +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));

select count(*) from gv$session s

where 1=1

and s.sid ||','|| s.serial#||',@'||s.inst_id ='1225,48073,@1'

set linesize 700 pagesize 300

col USERNAME_BLOCKER for a20

col MACHINE_BLOCKER for a35

col PROGRAM_BLOCKER for a35

col EXTERNAL_NAME_BLOCKER for a20

col USERNAME_BLOCKEE for a20

col MACHINE_BLOCKEE for a35

col PROGRAM_BLOCKEE for a35

col EXTERNAL_NAME_BLOCKEE for a20

col kill for a17

SELECT

(SELECT USERNAME FROM v$session WHERE SID=a.SID) USERNAME_BLOCKER,

(SELECT MACHINE FROM v$session WHERE SID=a.SID) MACHINE_BLOCKER,

(SELECT PROGRAM FROM v$session WHERE SID=a.SID) PROGRAM_BLOCKER,

(SELECT EXTERNAL_NAME FROM v$session WHERE SID=a.SID) EXTERNAL_NAME_BLOCKER,

-- a.sid SID_BLOCKER,

''''||a.sid ||','|| ''||',@'||a.inst_id ||'''' SID_BLOCKER, 'is blocking ' is_blocking ,

(SELECT USERNAME FROM v$session WHERE SID=b.SID) USERNAME_BLOCKEE,

(SELECT MACHINE FROM v$session WHERE SID=b.SID) MACHINE_BLOCKEE,

(SELECT PROGRAM FROM v$session WHERE SID=b.SID) PROGRAM_BLOCKEE,

(SELECT EXTERNAL_NAME FROM v$session WHERE SID=b.SID) EXTERNAL_NAME_BLOCKEE,

''''||b.sid ||','|| ''||',@'||b.inst_id ||'''' SID_BLOCKEE

from gv$lock a, gv$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

====

set lines 500

set pagesize 66

col inst format 99

col object_name format a20

col spid format a6

col type format a4

col machine format a30

col owner format a20

SELECT TO_CHAR(SYSDATE,'dd-MM-YY HH24:MI:SS') TM, u.username owner, o.object_name object_name, lid.*

FROM (SELECT

s.inst_id inst, s.SID, s.serial#, p.spid, s.blocking_session blocking_sid, s.machine, s.username, NVL (s.sql_id, 0) sqlid, s.sql_hash_value sqlhv,

DECODE (l.TYPE,

'TM', l.id1,

'TX', DECODE (l.request,

0, NVL (lo.object_id, -1),

s.row_wait_obj#

-1

) AS object_id,

l.TYPE type,

DECODE (l.lmode,

0, 'NONE',

1, 'NULL',

2, 'ROW SHARE',

3, 'ROW EXCLUSIVE',

4, 'SHARE',

5, 'SHARE ROW EXCLUSIVE',

6, 'EXCLUSIVE',

'?'

) mode_held,

DECODE (l.request,

0, 'NONE',

1, 'NULL',

2, 'ROW SHARE',

3, 'ROW EXCLUSIVE',

4, 'SHARE',

5, 'SHARE ROW EXCLUSIVE',

6, 'EXCLUSIVE',

'?'

) mode_requested,

l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,

s.row_wait_row#, s.row_wait_file#

FROM gv$lock l,

gv$session s,

gv$process p,

(SELECT object_id, session_id, xidsqn

FROM gv$locked_object

WHERE xidsqn > 0) lo

WHERE l.inst_id = s.inst_id

AND s.inst_id = p.inst_id

AND s.SID = l.SID

AND p.addr = s.paddr

AND l.SID = lo.session_id(+)

AND l.id2 = lo.xidsqn(+)) lid,

dba_objects o,

dba_users u

WHERE o.object_id(+) = lid.object_id

AND o.owner = u.username(+)

AND o.object_id <> -1


 
define 3="TIMESTAMP'2023-08-12 01:00:00'" 
define 4="TIMESTAMP'2023-08-12 13:29:00'"
 
 
 
 -- ash_enq_TX_rowdetail.sql
 
 define n=''
 set linesize 500
set pages 9999
column owner format a15
column object_name format a25
-- accept n prompt 'Enter number of days	:'

SELECT /*+ parallel(8) */ 
  o.owner ,
  o.object_name object_name ,
  CURRENT_FILE# file# ,
  CURRENT_BLOCK# block# ,
  CURRENT_ROW# row# ,
  COUNT(1)
FROM
  --gv$active_session_history ash,
  dba_hist_active_sess_history ash,
  dba_objects o
WHERE
  event            = 'enq: TX - row lock contention'
-- AND ash.sample_time >= systimestamp - &n 
and ash.sample_time between &3 and &4
AND o.object_id (+)= ash.CURRENT_obj#
GROUP BY
  o.owner,
  o.object_name,
  CURRENT_FILE#,
  CURRENT_BLOCK#,
  CURRENT_ROW#
ORDER BY
  COUNT(1);


 define n=''
 set linesize 500
set pages 9999
column owner format a15
column object_name format a25
-- accept n prompt 'Enter number of days	:'
col EVENT for a35

SELECT /*+ parallel(8) */ 
  o.owner ,
  o.object_name object_name ,
  CURRENT_FILE# file# ,
  CURRENT_BLOCK# block# ,
  CURRENT_ROW# row# ,
  COUNT(1),
  event
FROM
  --gv$active_session_history ash,
  dba_hist_active_sess_history ash,
  dba_objects o
WHERE 1=1
 -- and event  ='enq: TX - row lock contention'
   and event  like 'enq:%'
-- AND ash.sample_time >= systimestamp - &n 
and ash.sample_time between &3 and &4
AND o.object_id (+)= ash.CURRENT_obj#
GROUP BY
  o.owner,
  o.object_name,
  CURRENT_FILE#,
  CURRENT_BLOCK#,
  CURRENT_ROW#,
  event
ORDER BY
  COUNT(1);



  define owner='OS'
  define table_name='REPORT_DATA'
  define FILE=514
  define BLOCK=465745
  define row=12
  
SELECT
  *
FROM
  &owner..&table_name
WHERE
    dbms_rowid.rowid_relative_fno(rowid) = &file
AND dbms_rowid.rowid_block_number(ROWID) = &block
AND dbms_rowid.rowid_row_number(ROWID)   = &row
;

========




col owner format a15
col object format a30
col object_type format a10
col index_name format a30
col table_name format a30
col lockmode format a20
col event_name format a40 head 'EVENT NAME'
col sql_id format a13
col instance_number format 9999 head 'INST'

col p1 format a10
col p2 format a10
col p3 format a10
col p1text format a25
col p2text format a30
col p3text format a25

set linesize 200 trimspool on
set pagesize 100

with waits as (
	select
		sh.instance_number
		, sh.snap_id
		, sh.sample_id
		, sh.blocking_session
		, sh.sql_id
		, decode(bitand(sh.p1,65535),4,'ITL',6,'ROWLOCK','UNKNOWN') lockmode
		, count(*) * 10 waitcount -- only sampled every 10 seconds from gv$active_session_history
	from DBA_HIST_ACTIVE_SESS_HISTORY sh
	join dba_hist_snapshot s on s.snap_id = sh.snap_id
   	and s.snap_id = sh.snap_id
   	and s.instance_number = sh.instance_number
	where sh.blocking_session is not null
	--and sh.event_id = ( select event_id from v$event_name where name = 'enq: TX - row lock contention')
	and sh.event_id in ( select event_id from v$event_name where name like 'enq:%')
	--and s.begin_interval_time > sysdate -1/24
	and s.begin_interval_time > SYSDATE - INTERVAL '90' MINUTE
	--and s.begin_interval_time between to_date('d_begin_date','d_date_format') and to_date('d_end_date','d_date_format')
	group by 
		sh.instance_number
		, sh.snap_id
		, sh.sample_id
		, sh.blocking_session
		, sh.sql_id
		, decode(bitand(sh.p1,65535),4,'ITL',6,'ROWLOCK','UNKNOWN')
) ,
blockers as (
select distinct 
	w.sql_id
	, w.snap_id
	, w.sample_id
	, w.blocking_session
	, lockmode
	--, waitcount
from waits w
where 1=1
-- lockmode = 'ITL'
order by waitcount
)
select 
	h.sql_id
	, to_char(h.p1,'0XXXXXXX') p1
	, h.p1text
	, to_char(h.p2,'0XXXXXXX') p2
	, h.p2text
	, to_char(h.p3,'0XXXXXXX') p3
	, h.p3text
	, h.CURRENT_OBJ#
	, h.current_block#
from blockers b
join dba_hist_active_sess_history h
	on h.snap_id = b.snap_id
	--and h.sample_id = b.sample_id
	and h.session_id = b.blocking_session
	and h.sql_id = b.sql_id
order by h.sql_id, h.current_block#
/

*******************


alter session set nls_date_format='dd-mm-yyyy HH24:mi' ;
set pagesize 200 linesize 200
col "LOCk---" for a100
SELECT 
'Blocked Session			: '|| '' ||chr(10)||
'Kill_id    				: '||''''||h.sid ||','|| h.serial#||',@'||h.inst_id ||''''||chr(10)||
'LOGON					: '|| to_char(h.LOGON_TIME,'DD/MM/YYYY HH:MI:SS') ||chr(10)||
'PROCESS				: '|| p.SPID ||chr(10)||
'USERNAME				: '|| u.username ||chr(10)||
'OSUSER 				: '|| h.osuser ||chr(10)||
'MACHINE				: '|| h.machine ||chr(10)||
'PROGRAM				: '|| h.program ||chr(10)||
'MODULE					: '|| h.module ||chr(10)||
'Sql_id					: '|| h.Sql_id ||chr(10)||
'BLOCKING SESSION STATUS	        : '|| h.blocking_session_status ||chr(10)||
'BLOCKING INSTANCE			: '|| h.blocking_instance ||chr(10)||
'BLOCKING SESSION			: '|| h.blocking_session ||chr(10)||
'BLOCKING TIME				: '|| h.seconds_in_wait ||chr(10)||
'FINAL_BLOCKING_SESSION**>>>: '|| h.FINAL_BLOCKING_SESSION ||chr(10)||
'BLOCKING OBJECT: '|| o.owner||'.'||o.object_name ||chr(10)||
'SQL TEXT: '|| s.sql_text ||chr(10)||
  --  '                                                                                                                              '
 '*******Blockers Session: '|| 'Blockers_Session' ||chr(10)||
   'LOGON		: '|| to_char(h2.LOGON_TIME,'DD/MM/YYYY HH:MI:SS')  ||chr(10)||
   'Kill_id_b 	        : '||''''||h2.sid ||','|| h2.serial#||',@'||h2.inst_id ||''''||chr(10)||
   'PROCESS		: '|| p2.SPID ||chr(10)||
   'STATUS		: '|| h2.status ||chr(10)||
   'USERNAME	        : '|| u.username ||chr(10)||
   'OSUSER		: '|| h2.osuser ||chr(10)||
   'MACHINE		: '|| h2.machine ||chr(10)||
   'PROGRAM		: '|| h2.program ||chr(10)||
   'MODULE		: '|| h2.module ||chr(10)||
   'Sql_id		: '|| h2.Sql_id ||chr(10)||
   'prev_Sql_id	        : '|| h2.PREV_SQL_ID ||chr(10)||
   'SQL TEXT	        : '|| s2.sql_text "LOCk---"
   FROM
   gv$session h
INNER JOIN gv$session h2 on (h.blocking_instance=h2.inst_id and  h.blocking_session=h2.sid)
LEFT JOIN gv$SQLAREA s ON h.sql_hash_value = s.hash_value and h.sql_ADDRESS = s.ADDRESS and h.inst_id = s.inst_id 
LEFT JOIN gv$SQLAREA s2 ON h2.sql_hash_value = s2.hash_value and h2.sql_ADDRESS = s2.ADDRESS and h2.inst_id = s2.inst_id 
LEFT JOIN DBA_USERS u ON h.USER# = u.USER_ID
LEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id 
LEFT JOIN gv$process  p2 ON p2.ADDR = h2.PADDR and p2.inst_id = h2.inst_id 
LEFT JOIN dba_objects o ON o.object_id = h.row_wait_obj#
WHERE  1=1
--and h.seconds_in_wait > 600
and h.blocking_session_status = 'VALID';



prompt TX Lock -------- with sql 
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 400 pagesize 10000
col kill     for a15
col username for a15
col machine  for a35
col status   for a12
col module   for a20
col sql_text for a70 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.username, s.machine, s.sql_id,s.PREV_SQL_ID,g.type ,g.LMODE, g.REQUEST, g.CTIME, s.MODULE,g.BLOCK,s.STATUS,ADDR, KADDR,LOGON_TIME,LAST_CALL_ET,sq.sql_text 
--,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID
from gv$session s , gv$lock g,gv$sql sq
where s.inst_id  = g.inst_id
and   g.type     = 'TX'
and   g.sid      =  s.sid
and s.status!='INACTIVE'
and s.inst_id=sq.inst_id
and s.sql_id =sq.sql_id  
and   g.ctime > 30
union 
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.username, s.machine, s.sql_id,s.PREV_SQL_ID,g.type ,g.LMODE, g.REQUEST, g.CTIME, s.MODULE,g.BLOCK,s.STATUS,ADDR, KADDR,LOGON_TIME,LAST_CALL_ET,sq.sql_text 
--,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID
from gv$session s , gv$lock g,gv$sql sq
where s.inst_id  = g.inst_id
and   g.type     = 'TX'
and   g.sid      =  s.sid
and s.status='INACTIVE'
and s.inst_id=sq.inst_id
and ((sq.sql_id = s.sql_id  and sq.child_number = s.sql_child_number) or (sq.sql_id = s.prev_sql_id and sq.child_number = s.prev_child_number))
and   g.ctime > 30
order by ctime desc ;




set linesize 250 pagesize 200
col BLOCKING_STATUS for a175
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE 
|| ' ( session=' || ''''||s1.sid ||','|| s1.serial#||',@'||s1.inst_id ||''''||nvl(s1.sql_id,s1.prev_sql_id)||  ') is blocking-->> '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( session=' || ''''||s2.sid ||','|| s2.serial#||',@'||s2.inst_id ||''''||s2.sql_id|| ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE 1=1
and S1.SID=L1.SID 
and S2.SID=L2.SID
and S1.INST_ID=L1.INST_ID 
and S2.INST_ID=L2.INST_ID
and L1.BLOCK > 0 
and L2.REQUEST > 0
and L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;



set linesize 250 pagesize 200
col BLOCKING_STATUS for a175
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( session=' || ''''||s1.sid ||','|| s1.serial#||',@'||s1.inst_id ||''''||nvl(s1.sql_id,s1.prev_sql_id)||  ') is blocking-->> '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( session=' || ''''||s2.sid ||','|| s2.serial#||',@'||s2.inst_id ||''''||s2.sql_id|| ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

set linesize 300 
col BLOCKING_SESSION for a50
col BLOCKED_SESSION for a50
col BLOCKING_SESSION_KILL for a70
select blocking_session,blocked_session,script BLOCKING_SESSION_KILL from 
( select distinct 
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null) script ,count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid 
and s2.sid=l2.sid
and s1.inst_id=l1.inst_id 
and s2.inst_id=l2.inst_id
and l1.block > 0 
and l2.request > 0
and l1.id1 = l2.id1 
and l1.id2 = l2.id2 )
order by blocked_cnt desc;

set lines 500 
set pagesize 66 
col inst format 99
col object_name format a20
col spid format a6
col type format a4
col machine format a30
col owner format a20
SELECT TO_CHAR(SYSDATE,'MM/DD/YY HH24:MI:SS') TM, u.username owner, o.object_name object_name, lid.*
  FROM (SELECT
               s.inst_id inst, s.SID, s.serial#, p.spid, s.blocking_session blocking_sid, s.machine, s.username, NVL (s.sql_id, 0) sqlid, s.sql_hash_value sqlhv,
               DECODE (l.TYPE,
                       'TM', l.id1,
                       'TX', DECODE (l.request,
                                     0, NVL (lo.object_id, -1),
                                     s.row_wait_obj#
                                    ),
                       -1
                      ) AS object_id,
                 l.TYPE type,
               DECODE (l.lmode,
                       0, 'NONE',
                       1, 'NULL',
                       2, 'ROW SHARE',
                       3, 'ROW EXCLUSIVE',
                       4, 'SHARE',
                       5, 'SHARE ROW EXCLUSIVE',
                       6, 'EXCLUSIVE',
                       '?'
                      ) mode_held,
               DECODE (l.request,
                       0, 'NONE',
                       1, 'NULL',
                       2, 'ROW SHARE',
                      3, 'ROW EXCLUSIVE',
                       4, 'SHARE',
                       5, 'SHARE ROW EXCLUSIVE',
                       6, 'EXCLUSIVE',
                       '?'
                      ) mode_requested,
               l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,
               s.row_wait_row#, s.row_wait_file#
          FROM gv$lock l,
               gv$session s,
               gv$process p,
               (SELECT object_id, session_id, xidsqn
                  FROM gv$locked_object
                 WHERE xidsqn > 0) lo
         WHERE l.inst_id = s.inst_id
           AND s.inst_id = p.inst_id
           AND s.SID = l.SID
           AND p.addr = s.paddr
           AND l.SID = lo.session_id(+)
           AND l.id2 = lo.xidsqn(+)) lid,
       dba_objects o,
       dba_users u
WHERE o.object_id(+) = lid.object_id
AND o.owner = u.username(+)
AND o.object_id <> -1
/

PRO ##############################
PRO row lock from ash past 1 min
PRO ##############################

  set lines 300
  col program format a40
  col module format a20
  col event format a30
  select * from 
  (
  select count(*), session_id, program, module, CLIENT_ID, sql_id, event, mod(p1,16) as lock_mode, BLOCKING_SESSION from 
  v$active_Session_history
  where SAMPLE_TIME > sysdate - 1/1440
  group by session_id, program, module, CLIENT_ID, sql_id, event, mod(p1,16), BLOCKING_SESSION
  order by 1 desc
  )
  where rownum < 11;



set lines 300  pagesize 200
break on Kill on sid on  username on terminal
col Kill heading 'Kill String' 					format a13
col res heading 'Resource Type' 				format 999
col id1 										format 9999990
col id2 										format 9999990
col locking heading 'Lock Held/Lock Requested' 	format a40
col lmode heading 'Lock Held' 					format a15
col request heading 'Lock Requested' 			format a15
col serial# 					format 99999
col username  					format a10  heading "Username"
col terminal heading Term 			format a12
col tab 										format a30 heading "Table Name"

col owner 										format a9
col LAddr heading "ID1 - ID2" 					format a18
col Lockt heading "Lock Type" 					format a30
col command 							format a25
col sid 										format 9999
col kill 										for a15
select distinct
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,

s.con_id,
nvl(S.USERNAME,'Internal') username,
 --       L.SID,
        nvl(S.TERMINAL,'None') terminal,
        decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0, 'NO COMMAND',
1, 'CREATE TABLE',
2, 'INSERT', 
3,'ALTER SYSTEM',
4,'SYSTEM AUDIT',
5,'CREATE SESSION',
6,'ALTER SESSION',
7,'RESTRICTED SESSION',
8,'TABLE',
9,'CLUSTER',
10,'CREATE TABLESPACE',
11,'ALTER TABLESPACE',
12,'MANAGE TABLESPACE',
13,'DROP TABLESPACE',
14,'TABLESPACE',
15,'UNLIMITED TABLESPACE',
16,'USER',
17,'ROLLBACK SEGMENT',
18,'TYPE',
19,'INDEX',
20,'CREATE USER',
21,'BECOME USER',
22,'ALTER USER',
23,'DROP USER',
24,'SYNONYM',
25,'PUBLIC SYNONYM',
26,'VIEW',
27,'SEQUENCE',
28,'DATABASE LINK',
29,'PUBLIC DATABASE LINK',
30,'CREATE ROLLBACK SEGMENT',
31,'ALTER ROLLBACK SEGMENT',
32,'DROP ROLLBACK SEGMENT',
33,'ROLE',
34,'DIMENSION',
35,'PROCEDURE',
36,'TRIGGER',
37,'PROFILE',
38,'DIRECTORY',
39,'MATERIALIZED VIEW',
40,'CREATE TABLE',
41,'CREATE ANY TABLE',
42,'ALTER ANY TABLE',
43,'BACKUP ANY TABLE',
44,'DROP ANY TABLE',
45,'LOCK ANY TABLE',
46,'COMMENT ANY TABLE',
47,'SELECT ANY TABLE',
48,'INSERT ANY TABLE',
49,'UPDATE ANY TABLE',
50,'DELETE ANY TABLE',
51,'OUTLINE',
52,'MINING MODEL',
54,'ALTER TABLE',
57,'LOCK TABLE',
58,'COMMENT TABLE',
60,'CREATE CLUSTER',
61,'CREATE ANY CLUSTER',
62,'ALTER ANY CLUSTER',
63,'DROP ANY CLUSTER',
65,'SELECT TABLE',
66,'INSERT TABLE',
67,'UPDATE TABLE',
68,'DELETE TABLE',
69,'GRANT TABLE',
71,'CREATE ANY INDEX',
72,'ALTER ANY INDEX',
73,'DROP ANY INDEX',
77,'NOT EXISTS',
78,'ALL STATEMENTS',
80,'CREATE SYNONYM',
81,'CREATE ANY SYNONYM',
82,'DROP ANY SYNONYM',
83,'SYSDBA',
84,'SYSOPER',
85,'CREATE PUBLIC SYNONYM',
86,'DROP PUBLIC SYNONYM',
90,'CREATE VIEW',
91,'CREATE ANY VIEW',
92,'DROP ANY VIEW',
93,'CREATE JAVA SOURCE',
94,'CREATE JAVA CLASS',
95,'CREATE JAVA RESOURCE',
96,'ALTER JAVA SOURCE',
97,'ALTER JAVA CLASS',
98,'ALTER JAVA RESOURCE',
99,'DROP JAVA SOURCE',
100,'DROP JAVA CLASS',
101,'DROP JAVA RESOURCE',
103,'ALTER SEQUENCE',
104,'SELECT SEQUENCE',
105,'CREATE SEQUENCE',
106,'CREATE ANY SEQUENCE',
107,'ALTER ANY SEQUENCE',
108,'DROP ANY SEQUENCE',
109,'SELECT ANY SEQUENCE',
111,'GRANT SEQUENCE',
115,'CREATE DATABASE LINK',
120,'CREATE PUBLIC DATABASE LINK',
121,'DROP PUBLIC DATABASE LINK',
125,'CREATE ROLE',
126,'DROP ANY ROLE',
127,'GRANT ANY ROLE',
128,'ALTER ANY ROLE',
130,'AUDIT ANY',
131,'SYSTEM GRANT',
135,'ALTER DATABASE',
138,'FORCE TRANSACTION',
139,'FORCE ANY TRANSACTION',
140,'CREATE PROCEDURE',
141,'CREATE ANY PROCEDURE',
142,'ALTER ANY PROCEDURE',
143,'DROP ANY PROCEDURE',
144,'EXECUTE ANY PROCEDURE',
146,'EXECUTE PROCEDURE',
147,'GRANT PROCEDURE',
151,'CREATE TRIGGER',
152,'CREATE ANY TRIGGER',
153,'ALTER ANY TRIGGER',
154,'DROP ANY TRIGGER',
160,'CREATE PROFILE',
161,'ALTER PROFILE',
162,'DROP PROFILE',
163,'ALTER RESOURCE COST',
165,'ANALYZE ANY',
167,'GRANT ANY PRIVILEGE',
172,'CREATE MATERIALIZED VIEW',
173,'CREATE ANY MATERIALIZED VIEW',
174,'ALTER ANY MATERIALIZED VIEW',
175,'DROP ANY MATERIALIZED VIEW',
176,'NETWORK',
177,'CREATE ANY DIRECTORY',
178,'DROP ANY DIRECTORY',
179,'GRANT DIRECTORY',
180,'CREATE TYPE',
181,'CREATE ANY TYPE',
182,'ALTER ANY TYPE',
183,'DROP ANY TYPE',
184,'EXECUTE ANY TYPE',
186,'UNDER ANY TYPE',
187,'GRANT TYPE',
188,'CREATE LIBRARY',
189,'CREATE ANY LIBRARY',
190,'ALTER ANY LIBRARY',
191,'DROP ANY LIBRARY',
192,'EXECUTE ANY LIBRARY',
193,'EXECUTE LIBRARY',
200,'CREATE OPERATOR',
201,'CREATE ANY OPERATOR',
202,'ALTER ANY OPERATOR',
203,'DROP ANY OPERATOR',
204,'EXECUTE ANY OPERATOR',
205,'CREATE INDEXTYPE',
206,'CREATE ANY INDEXTYPE',
207,'ALTER ANY INDEXTYPE',
208,'DROP ANY INDEXTYPE',
209,'UNDER ANY VIEW',
210,'QUERY REWRITE',
211,'GLOBAL QUERY REWRITE',
212,'EXECUTE ANY INDEXTYPE',
213,'UNDER ANY TABLE',
214,'CREATE DIMENSION',
215,'CREATE ANY DIMENSION',
216,'ALTER ANY DIMENSION',
217,'DROP ANY DIMENSION',
218,'MANAGE ANY QUEUE',
219,'ENQUEUE ANY QUEUE',
220,'DEQUEUE ANY QUEUE',
221,'CONTEXT',
222,'CREATE ANY CONTEXT',
223,'DROP ANY CONTEXT',
224,'CREATE ANY OUTLINE',
225,'ALTER ANY OUTLINE',
226,'DROP ANY OUTLINE',
227,'ADMINISTER RESOURCE MANAGER',
228,'ADMINISTER DATABASE TRIGGER',
233,'MERGE ANY VIEW',
234,'ON COMMIT REFRESH',
235,'EXEMPT ACCESS POLICY',
236,'RESUMABLE',
237,'SELECT ANY DICTIONARY',
238,'DEBUG CONNECT SESSION',
241,'DEBUG ANY PROCEDURE',
242,'DEBUG PROCEDURE',
243,'FLASHBACK ANY TABLE',
244,'GRANT ANY OBJECT PRIVILEGE',
245,'CREATE EVALUATION CONTEXT',
246,'CREATE ANY EVALUATION CONTEXT',
247,'ALTER ANY EVALUATION CONTEXT',
248,'DROP ANY EVALUATION CONTEXT',
249,'EXECUTE ANY EVALUATION CONTEXT',
250,'CREATE RULE SET',
251,'CREATE ANY RULE SET',
252,'ALTER ANY RULE SET',
253,'DROP ANY RULE SET',
254,'EXECUTE ANY RULE SET',
255,'EXPORT FULL DATABASE',
256,'IMPORT FULL DATABASE',
257,'CREATE RULE',
258,'CREATE ANY RULE',
259,'ALTER ANY RULE',
260,'DROP ANY RULE',
261,'EXECUTE ANY RULE',
262,'ANALYZE ANY DICTIONARY',
263,'ADVISOR',
264,'CREATE JOB',
265,'CREATE ANY JOB',
266,'EXECUTE ANY PROGRAM',
267,'EXECUTE ANY CLASS',
268,'MANAGE SCHEDULER',
269,'SELECT ANY TRANSACTION',
270,'DROP ANY SQL PROFILE',
271,'ALTER ANY SQL PROFILE',
272,'ADMINISTER SQL TUNING SET',
273,'ADMINISTER ANY SQL TUNING SET',
274,'CREATE ANY SQL PROFILE',
275,'EXEMPT IDENTITY POLICY',
276,'MANAGE FILE GROUP',
277,'MANAGE ANY FILE GROUP',
278,'READ ANY FILE GROUP',
279,'CHANGE NOTIFICATION',
280,'CREATE EXTERNAL JOB',
281,'CREATE ANY EDITION',
282,'DROP ANY EDITION',
283,'ALTER ANY EDITION',
284,'CREATE ASSEMBLY',
285,'CREATE ANY ASSEMBLY',
286,'ALTER ANY ASSEMBLY',
287,'DROP ANY ASSEMBLY',
288,'EXECUTE ANY ASSEMBLY',
289,'EXECUTE ASSEMBLY',
290,'CREATE MINING MODEL',
291,'CREATE ANY MINING MODEL',
292,'DROP ANY MINING MODEL',
293,'SELECT ANY MINING MODEL',
294,'ALTER ANY MINING MODEL',
295,'COMMENT ANY MINING MODEL',
296,'ALTER MINING MODEL',
297,'COMMENT MINING MODEL',
298,'GRANT MINING MODEL',
299,'SELECT MINING MODEL',
301,'CREATE CUBE DIMENSION',
302,'ALTER ANY CUBE DIMENSION',
303,'CREATE ANY CUBE DIMENSION',
304,'DELETE ANY CUBE DIMENSION',
305,'DROP ANY CUBE DIMENSION',
306,'INSERT ANY CUBE DIMENSION',
307,'SELECT ANY CUBE DIMENSION',
308,'CREATE CUBE',
309,'ALTER ANY CUBE',
310,'CREATE ANY CUBE',
311,'DROP ANY CUBE',
312,'SELECT ANY CUBE',
313,'UPDATE ANY CUBE',
314,'CREATE MEASURE FOLDER',
315,'CREATE ANY MEASURE FOLDER',
316,'DELETE ANY MEASURE FOLDER',
317,'DROP ANY MEASURE FOLDER',
318,'INSERT ANY MEASURE FOLDER',
319,'CREATE CUBE BUILD PROCESS',
320,'CREATE ANY CUBE BUILD PROCESS',
321,'DROP ANY CUBE BUILD PROCESS',
322,'UPDATE ANY CUBE BUILD PROCESS',
323,'COMMENT EDITION',
324,'GRANT EDITION',
325,'USE EDITION',
326,'UPDATE ANY CUBE DIMENSION',
327,'ADMINISTER SQL MANAGEMENT OBJECT',
328,'ALTER PUBLIC DATABASE LINK',
329,'ALTER DATABASE LINK',
330,'DIRECT_PATH LOAD',
331,'DIRECT_PATH UNLOAD',
350,'FLASHBACK ARCHIVE ADMINISTER',
351,'EXEMPT REDACTION POLICY',
command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt,
s.sql_id,s.PREV_SQL_ID
from    GV$LOCK L, GV$SESSION S,SYS.USER$ U1, SYS.OBJ$ T1
where   L.SID = S.SID
and     l.inst_id=s.inst_id
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1)
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
-- and S.TERMINAL is not null
and command!=0
and S.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by 1,2,5;

=====


set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col BLOCKING_TREE for a80
col SQL_TEXT for a60
col EVENT for a34

with L as
 (select--+ materialize
         L1.inst_id, L1.sid
    from gv$lock L1, gv$lock L2
   where L1.block > 0
     and L1.ID1 = L2.ID1
     and L1.ID2 = L2.ID2
     and L2.type = 'TX'
     and L2.REQUEST > 0)
select--+ opt_param('_connect_by_use_union_all' 'false')
-- LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || s.inst_id || ' SID#' || sid || ' ' ||
 LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''|| ' ' ||
 program as BLOCKING_TREE,
 EVENT,
 last_call_et,
 blocking_session_status,
 nvl(s.sql_id,PREV_SQL_ID) PREV_Current_SQL_ID ,
 --s.event,
 substr(trim(NVL(sa1.sql_text,sa2.sql_text)), 1, 60) SQL_TEXT,
 decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid
--       prior status
  from gv$session s, gv$sqlarea sa1, gv$sqlarea sa2
 where s.sql_id = sa1.sql_id(+)
   and s.inst_id = sa1.inst_id(+)
   and s.prev_sql_id = sa2.sql_id(+)
   and s.inst_id = sa2.inst_id(+)
connect by prior sid = blocking_session
 start with (s.inst_id, s.sid)
            in (select inst_id, sid from L)
/



col process for a15
col terminal for a25
col lmode for a20
col object for a30
set lin 1000
col kill for a18
col USERNAME for a20
col LREQUEST for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,s.sql_id,
       decode(s.process, null,  decode(substr(p.username,1,1), '?',   upper(s.osuser), p.username), decode(p.username, 'ORACUSR ', upper(s.osuser), s.process)
       ) process,
       nvl(s.osuser, 'SYS ('||substr(p.username,1,4)||')') username,
       decode(s.terminal, null, rtrim(p.terminal, chr(0)),
              upper(s.terminal)) terminal,
       decode(l.type, 'MR', decode(u.name, null,
                            'DICTIONARY OBJECT', u.name||'.'||o.name),
                      'TD', u.name||'.'||o.name,
                      'TM', u.name||'.'||o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                      ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE','NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) object,
       decode(l.type,
          -- Long locks major
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks minor
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
       decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,      
       decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest ,s.event
           from   gv$lock l, gv$session s, obj$ o, user$ u, gv$process p
where  s.paddr  = p.addr(+)
and  l.sid    = s.sid
and  l.inst_id    = s.inst_id
and  l.id1    = o.obj#(+)
and  o.owner# = u.user#(+)
and  l.type   <> 'MR'
and p.background!=1
UNION ALL                          /*** LATCH HOLDERS SQL ***/
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id,s.sql_id, s.process, s.osuser, s.terminal,'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr),s.event
from   gv$process p, gv$session s, gv$latchholder h
where  h.pid  = p.pid
and h.inst_id  = p.inst_id
and  p.addr = s.paddr
and p.background!=1
UNION ALL                         /*** LATCH WAITERS SQL ***/
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id,s.sql_id, s.process, s.osuser, s.terminal,  'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait,s.event
from   gv$session s, gv$process p, gv$latch l
where  latchwait is not null
and  p.addr      = s.paddr
and  p.inst_id      = s.inst_id
and  p.latchwait = l.addr
and p.background!=1
/

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 20 May 2012

Oracle Lock info

Oracle Lock info and oracle wait due to locks

Oracle DBA

anuj blog Archive