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

Things to note when desinging a module

Some points to consider when we designing or implementing application. These points will helpful to diagnose or fix issues in production environment. Will update whenever my code bite back :)

1. Externalizing configuration values in database or .properties or XML files.
2. Testing the application with the volume of data in production.
3. Set proper timeout functions 
4. Set limit to retry functions to connect to third party or external services 
5. Browser compatibility checking
6. Proper database design Column to indicate created, updated time and user details, Active or delete flag
7. Security