Check locked Objects in Oracle database

LOCKED DATABASE OBJECTS

SELECT l.session_id || ',' || v.serial# sid_serial,       
       l.ORACLE_USERNAME ora_user,       
       o.object_name,       
       o.object_type,       
       DECODE(l.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(l.locked_mode)              
              ) lock_mode,       
       o.status,       
       to_char(o.last_ddl_time, 'dd.mm.yy') last_ddl
  FROM dba_objects o, gv$locked_object l, v$session v
 WHERE o.object_id = l.object_id      
   and l.SESSION_ID = v.sid;

select a.sid, a.serial#, OBJECT_NAME , a.*
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
 
ALTER SYSTEM KILL SESSION 'sid,serial#';

SELECT T.*, sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.BIND_DATA
  FROM (select c.owner,
               c.object_name,
               c.object_type,
               b.sid,
               b.serial#,
               b.status,
               a.ORACLE_USERNAME,
               b.osuser,
               b.machine,
               DECODE(a.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(a.locked_mode)) alock_mode,
               b.STATUS SESSION_STATUS,
               b.LOGON_TIME,
               b.SQL_HASH_VALUE
          from v$locked_object a, v$session b, dba_objects c
         where b.sid = a.session_id
           and a.object_id = c.object_id) T
  left join v$sqlarea sa
    ON sa.HASH_VALUE = t.SQL_HASH_VALUE;
 

select a.sid, a.serial#, OBJECT_NAME, a.LOGON_TIME
  from v$session a, v$locked_object b, dba_objects c
 where b.object_id = c.object_id
   and a.sid = b.session_id;
    
    
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION
  FROM v$session
 WHERE BLOCKING_SESSION IS NOT NULL;
  
  
 SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions
  FROM v$lock l1, v$lock l2
 WHERE l1.block = 1
   AND l2.request > 0
   AND l1.id1 = l2.id1
   AND l1.id2 = l2.id2;

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 l1.id2 = l2.id2;
    

select * from v$lock where type='TX' and request>0;
select * from v$lock where type='TX' and lmode>0;

Reference:

• Finding oracle locked objects