Search This Blog

Total Pageviews

Saturday 26 May 2012


Oracle Lock and Enqueue info  ..... 








"enq: TX - row lock contention" due to lock 


col SQL_TEXT format a50
col OBJECT format a15
col EVENT format a35
SELECT sid, event, wait_time_micro / 1000 time_ms, blocking_session, object_type || ': ' || object_name object, sql_text
      FROM v$session s
      LEFT OUTER JOIN v$sql USING (sql_id)
      LEFT OUTER JOIN dba_objects  ON (object_id = row_wait_obj#)
      WHERE event LIKE 'enq: %';

   Sess
    ID  EVENT                                  TIME_MS BLOCKING_SESSION OBJECT          SQL_TEXT
------- ----------------------------------- ---------- ---------------- --------------- --------------------------------------------------
     37 enq: TX - row lock contention        2,601,852               17 TABLE: ANUJ     update anuj set sal=1001 where EMPNO=7369



WITH sql_app_waits AS
        (SELECT sql_id, SUBSTR(sql_text, 1, 80) sql_text,application_wait_time/1000 app_time_ms,elapsed_time,
                ROUND(application_wait_time * 100 / elapsed_time, 2) app_time_pct,
                ROUND(application_wait_time * 100 /SUM(application_wait_time) OVER (), 2) pct_of_app_time,
                RANK() OVER (ORDER BY application_wait_Time DESC) ranking
         FROM v$sql
         WHERE elapsed_time > 0 AND application_wait_time>0 )
  SELECT sql_text, app_time_ms, app_time_pct,pct_of_app_time
   FROM sql_app_waits
   WHERE ranking <= 10
   ORDER BY ranking  ;


SQL_TEXT                                           APP_TIME_MS APP_TIME_PCT PCT_OF_APP_TIME
-------------------------------------------------- ----------- ------------ ---------------
update anuj set sal=1001 where EMPNO=7369           2625757.49          100             100



select distinct a.sid "waiting sid",d.sql_text "waiting SQL",a.ROW_WAIT_OBJ# "locked object",a.BLOCKING_SESSION "blocking sid",c.sql_text "SQL from blocking 
session" from v$session a, v$active_session_history b, v$sql c, v$sql d
where
a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#


WITH ash_query AS (
         SELECT substr(event,6,2) lock_type,program, h.module, h.action, object_name,
                SUM(time_waited)/1000 time_ms, COUNT( * ) waits, username, sql_text,
                RANK() OVER (ORDER BY SUM(time_waited) DESC)    AS time_rank,
                ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))  OVER (), 2) pct_of_time
          FROM  v$active_session_history h
         JOIN  dba_users u  USING (user_id)
         LEFT OUTER JOIN dba_objects o
              ON (o.object_id = h.current_obj#)
         LEFT OUTER JOIN v$sql s USING (sql_id)
        WHERE event LIKE 'enq: %'
        GROUP BY substr(event,6,2) ,program, h.module, h.action,object_name,  sql_text, username)
 SELECT lock_type,module, username,  object_name, time_ms, pct_of_time, sql_text
 FROM ash_query
 WHERE time_rank < 11
 ORDER BY time_rank;

                           OBJ NAME or
LOCK MODULE       Username TRANS_ID        TIME_MS PCT_OF_TIME SQL_TEXT
---- ------------ -------- ------------ ---------- ----------- --------------------------------------------------
PR                SYS      KET$_AUTOTAS      2,165      100.00 SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_T
                                                               IME, SYSTIMESTAMP) - INTERVAL '1' SECOND AS SHUTDO
                                                               WN_TIME FROM (SELECT RRI.INSTANCE_NAME AS INSTANCE
                                                               _NAME, RRI.HOST_NAME AS HOST_NAME, FROM_TZ(RRI.STA
                                                               RTUP_TIME, '+00:00') AS RRI_STARTUP_TIME, DBMS_HA_
                                                               ALERTS_PRVT.INSTANCE_STARTUP_TIMESTAMP_TZ(GVI.STAR
                                                               TUP_TIME) AS GVI_STARTUP_TIME FROM RECENT_RESOURCE
                                                               _INCARNATIONS$ RRI LEFT OUTER JOIN GV$INSTANCE GVI
                                                                ON GVI.INSTANCE_NAME = RRI.RESOURCE_NAME WHERE RR
                                                               I.RESOURCE_TYPE = 'INSTANCE' AND :B2 = RRI.DB_UNIQ
                                                               UE_NAME AND :B1 = RRI.DB_DOMAIN) WHERE GVI_STARTUP
                                                               _TIME IS NULL OR GVI_STARTUP_TIME > RRI_STARTUP_TI
                                                               ME GROUP BY INSTANCE_NAME, HOST_NAME, GVI_STARTUP_
                                                               TIME

TX   SQL*Plus     ANUJ     ANUJ                  0         .00 update anuj set sal=1001 where EMPNO=7369





col USERNAME for a20 
col OBJECT_NAME for a20
col SQL_TEXT for a70 wrap
col MODULE for a15
WITH ash_query AS (
     SELECT substr(event,6,2) lock_type,program, 
            h.module, h.action,   object_name,
            SUM(time_waited)/1000 time_ms, COUNT( * ) waits, 
            username, sql_text,
            RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
            ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited)) 
                OVER (), 2)             pct_of_time
      FROM  gv$active_session_history h 
      JOIN  dba_users u  USING (user_id)
      LEFT OUTER JOIN dba_objects o
           ON (o.object_id = h.current_obj#)
      LEFT OUTER JOIN gv$sql s USING (sql_id)
     WHERE event LIKE 'enq: %'
     GROUP BY substr(event,6,2) ,program, h.module, h.action,     object_name,  sql_text, username)
SELECT lock_type,module, username,  object_name, time_ms,pct_of_time,
         sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;



col sid format a10
 WITH sessions AS
    (SELECT /*+materialize*/    sid, blocking_session, row_wait_obj#, sql_id    FROM v$session)
    SELECT LPAD(' ', LEVEL*3 ) || sid sid, object_name,substr(sql_text,1,40) sql_text
    FROM sessions s
    LEFT OUTER JOIN dba_objects  ON (object_id = row_wait_obj#)
    LEFT OUTER JOIN v$sql USING (sql_id)
    WHERE sid IN (SELECT blocking_session FROM sessions)  OR blocking_session IS NOT NULL
    CONNECT BY PRIOR sid = blocking_session
    START WITH blocking_session IS NULL;

Sess       OBJ NAME or
ID         TRANS_ID     SQL_TEXT
---------- ------------ ----------------------------------------------------------------------
 17
  37       ANUJ         update anuj set sal=1001 where EMPNO=736



 

Oracle Date Calculations ....


Oracle Date Calculations .... 


Oracle Date's Maths with Anuj
Oracle Date Calculations ....
Oracle Date's Maths with Anuj
Oracle Date's Maths .....
Oracle Date Calculations ....

Oracle Date's Maths with Anuj
Oracle Date Calculations ....
Oracle Date's Maths with Anuj
Oracle Date's Maths .....
Oracle Date Calculations ....



select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate ,TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS') "ssysdate-1" from dual;

SSYSDATE            ssysdate-1
------------------- -------------------
26-05-2012 16:45:20 25-05-2012 16:45:20



so " -1 " will subtract 1 day from sysdate .

suppose we want to subtract 1 minute , then we have to convert 1day into 1 minute

by dividing by ( 1day = 24 hr ,1hr = 60 min )


1/(24*60)

or

SQL> select 24*60 from dual;

24*60
----------
1440


SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS')     "ssysdate" ,
    TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS')          "SSYSDATE-1day",
    TO_CHAR(sysdate -1/(24*60),'DD-MM-YYYY HH24:MI:SS')  "SSYSDATE-1minute",
    TO_CHAR(sysdate -1/1440,'DD-MM-YYYY HH24:MI:SS')     "SSYSDATE-1minute",
    TO_CHAR(sysdate -10/1440,'DD-MM-YYYY HH24:MI:SS')    "SSYSDATE-10minute" 
    from dual; 

ssysdate            SSYSDATE-1day       SSYSDATE-1minute    SSYSDATE-1minute    SSYSDATE-10minute
------------------- ------------------- ------------------- ------------------- -------------------
26-05-2012 16:45:50 25-05-2012 16:45:50 26-05-2012 16:44:50 26-05-2012 16:44:50 26-05-2012 16:35:50




Subtract 1 Second 

SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate,TO_CHAR(sysdate -1/(24*60*60),'DD-MM-YYYY HH24:MI:SS') "SSYSDATE-1 second" from dual; 


SSYSDATE            SSYSDATE-1 second
------------------- -------------------
26-05-2012 16:46:15 26-05-2012 16:46:14



select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:46:36 26-may-2012 05:46:36



 select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:47:07 26-may-2012 05:47:07




SQL> create table ddate ( x date );

Table created.

SQL> insert into ddate values ('1-jan-11') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(x,'dd-mon-yyyy hh:mi:ss') ssysdate , to_char(x + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from ddate;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
01-jan-2011 12:00:00 01-jan-2011 01:00:00




 set linesize 300
 ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 
 SELECT SYSDATE             as current_date,
       SYSDATE - 1          as minus_1_day,
       SYSDATE - 2/24       as minus_2_hours,
       SYSDATE - 10/24/60   as minus_10_minutes,
       SYSDATE - 1/24/60    as minus_1_minutes,
       SYSDATE  - 30/24/60/60 as minus_30_seconds
FROM   dual;
 
CURRENT_DATE         MINUS_1_DAY          MINUS_2_HOURS        MINUS_10_MINUTES     MINUS_1_MINUTES      MINUS_30_SECONDS
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
05-FEB-2021 07:14:10 04-FEB-2021 07:14:10 05-FEB-2021 05:14:10 05-FEB-2021 07:04:10 05-FEB-2021 07:13:10 05-FEB-2021 07:13:40




===

 

 
 set linesize 300 
 SELECT 
   TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.M.') AS "Uppercase1"
   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS a.m.') AS "Lowercase1"
   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.m.') AS "Mixed1"
  ,  TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.M.') AS "Uppercase"
  , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS a.m.') AS "Lowercase"
 , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.m.') AS "Mixed"
FROM DUAL;



Uppercase1    Lowercase1    Mixed1        Uppercase                Lowercase                Mixed
------------- ------------- ------------- ------------------------ ------------------------ ------------------------
06:30:45 P.M. 06:30:45 p.m. 06:30:45 P.M. 2020-12-30 06:30:45 P.M. 2020-12-30 06:30:45 p.m. 2020-12-30 06:30:45 P.M.

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

Session altered.

SQL> 
select sysdate , sysdate-1/24,sysdate-.5/24,sysdate-.25/24 from dual;

SYSDATE             SYSDATE-1/24        SYSDATE-.5/24       SYSDATE-.25/24
------------------- ------------------- ------------------- -------------------
26-06-2023 05:34:38 26-06-2023 04:34:38 26-06-2023 05:04:38 26-06-2023 05:19:38

SQL>

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

select to_char(sysdate+n,'DAY','nls_date_language=ENGLISH') day
     from (select level n from dual connect by level <= 7)
    /

DAY
------------------------------------
WEDNESDAY
THURSDAY
FRIDAY
SATURDAY
SUNDAY
MONDAY
TUESDAY





Oracle Lock info In RAC ..... 



for session id in sqlplus
col sid format a12
select sys_context('USERENV','SID') as sid from dual


====================================================
col  Kill_Command format a35
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# || ''' 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 ;
 
LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS   WAITING_INST WAITER_SID WAITER_LOCK_TYPE      WAITER_MODE_REQ     Kill_Command
------------ ----------- -------------- -------- ------------ ---------- --------------------- ------------------- -----------------------------------
           1          17              5 INACTIVE            1         37 Transaction           Exclusive           alter system kill session '17,5' im
                                                                                                                   mediate;
 
 

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,
       p2
  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';







Oracle DBA

anuj blog Archive