DB Locks

PT :  Identificar DB Locks
EN :  Identify DB Locks

Blocking sessions

select
s1.status,
s1.logon_time,
s1.last_call_et seconds_since_active,
s1.module,
s1.username || '@' || s1.machine || ' with ' || s1.PROGRAM || ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' || ' conn with ' || s2.PROGRAM AS blocking_status,
'alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as kill_sess,
'alter system disconnect session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as disconnect_sess
from
    gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
where
s1.type='USER'
and s1.status='INACTIVE'
--and S1.MODULE like 'HH%'
and 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
order by 3 desc;

Blocked Objects

select
       b.session_id as sid,
       username,
       a.owner as object_owner,
       a.object_name,
       decode(b.locked_mode, 0, 'none',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) as locked_mode,
       b.os_user_name,
       s.username,
       s.program,
       s.module,
       'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate ;' as kill_sess
FROM   dba_objects a,
       gv$locked_object b,
       gv$session s
WHERE
a.object_id = b.object_id and b.session_id=s.sid
and s.SID in (
select
 s1.sid
from
    gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
where
s1.type='USER'
and s1.status='INACTIVE'
and S1.MODULE like 'HH%'
and 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 )

Blocked objects by Session

select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate ;' as kill_sess,
       b.session_id as sid,
       nvl(b.oracle_username, '(oracle)') as username,
       a.owner as object_owner,
       a.object_name,
       decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) as locked_mode,
       b.os_user_name,
       s.username
from   dba_objects a,
       gv$locked_object b,
       gv$session s
where
a.object_id = b.object_id and b.session_id=s.sid
and s.sid=1234
order by 1, 2, 3, 4;