1- Get current session id.

SELECT SID, SERIAL# FROM V$SESSION WHERE SID = Sys_Context('USERENV', 'SID');

2- Create Below Table and Insert a Row Without Commit or Rollback.

create table lock_test(
  id# number primary key,
  value varchar2(20)
);

3- Insert into table without commit or rollback.

3- insert into lock_test values (1, 'Insert lock test');

4- Open new session and insert same record in the same table.

3- insert into lock_test values (1, 'Insert lock test');

5- Find Blocked Sessions.

SELECT    SESLCK.USERNAME|| '@'|| SESLCK.MACHINE|| '@INSTANCE'|| SESLCK.INST_ID|| ' (SID='|| SESLCK.SID|| ' SERIAL='|| SESLCK.SERIAL#
       || ' STATUS='|| SESLCK.STATUS|| ') IS BLOCKING '|| SEWT.USERNAME|| '@'|| SEWT.MACHINE|| '@INSTANCE'|| SEWT.INST_ID|| ' (SID='
       || SEWT.SID|| ' SERIAL='|| SEWT.SERIAL#|| ' STATUS='|| SEWT.STATUS|| ' SQLID='|| SEWT.SQL_ID|| ')'LOCK_INFORMATION
  FROM GV$LOCK     WT,
       GV$LOCK     LCKR,
       GV$SESSION  SESLCK,
       GV$SESSION  SEWT
 WHERE     LCKR.ID1 = WT.ID1
       AND LCKR.SID = SESLCK.SID
       AND LCKR.INST_ID = SESLCK.INST_ID
       AND WT.SID = SEWT.SID
       AND WT.INST_ID = SEWT.INST_ID
       AND LCKR.ID2 = WT.ID2
       AND LCKR.REQUEST = 0
       AND WT.LMODE = 0;

6- Find Lock Wait Time

SELECT BLOCKING_SESSION         "BLOCKING_SESSION",
         SID                      "BLOCKED_SESSION",
         SECONDS_IN_WAIT / 60     "WAIT_TIME(MINUTES)"
    FROM GV$SESSION
   WHERE BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;

7- Find Blocking SQL

SELECT *
    FROM GV$OPEN_CURSOR OC
   WHERE     OC.INST_ID = :TYPE_BLOCKING_INSTANCE_ID
         AND OC.SID = :TYPE_BLOCKING_SID
         AND (   OC.SQL_TEXT LIKE 'INSERT%'
              OR OC.SQL_TEXT LIKE 'UPDATE%'
              OR OC.SQL_TEXT LIKE 'DELETE%')
         AND OC.CURSOR_TYPE = 'OPEN'
ORDER BY OC.LAST_SQL_ACTIVE_TIME;

8- Find Blocked SQL

SELECT SES.SID,
       SES.SERIAL#     SER#,
       SES.PROCESS     OS_ID,
       SES.STATUS,
       SQL.SQL_FULLTEXT
  FROM GV$SESSION SES, GV$SQL SQL, GV$PROCESS PRC
 WHERE     SES.SQL_ID = SQL.SQL_ID
       AND SES.SQL_HASH_VALUE = SQL.HASH_VALUE
       AND SES.PADDR = PRC.ADDR
       AND SES.INST_ID = SQL.INST_ID
       AND SES.SID = &ENTER_BLOCKED_SESSION_SID;

9- Find Locked Table

SELECT LO.SESSION_ID,
         LO.ORACLE_USERNAME,
         LO.OS_USER_NAME,
         LO.PROCESS,
         DO.OBJECT_NAME,
         DO.OWNER,
         DECODE (LO.LOCKED_MODE,
                 0, 'NONE',
                 1, 'NULL',
                 2, 'ROW SHARE (SS)',
                 3, 'ROW EXCL (SX)',
                 4, 'SHARE',
                 5, 'SHARE ROW EXCL (SSX)',
                 6, 'EXCLUSIVE',
                 TO_CHAR (LO.LOCKED_MODE))
             MODE_HELD
    FROM GV$LOCKED_OBJECT LO, DBA_OBJECTS DO
   WHERE LO.OBJECT_ID = DO.OBJECT_ID
ORDER BY 5;

10- kill blocking session inside Oracle.

 SELECT  'ALTER SYSTEM KILL SESSION '''|| SESLCK.SID|| ','|| SESLCK.SERIAL#|| ',@'|| SESLCK.INST_ID|| ''' IMMEDIATE;'
      BLOCKING_SESSION_KILL_COMMAND
 FROM GV$LOCK   WT,
    GV$LOCK   LCKR,
    GV$SESSION SESLCK,
    GV$SESSION SEWT
WHERE   LCKR.ID1 = WT.ID1
    AND LCKR.SID = SESLCK.SID
    AND LCKR.INST_ID = SESLCK.INST_ID
    AND WT.SID = SEWT.SID
    AND WT.INST_ID = SEWT.INST_ID
    AND LCKR.ID2 = WT.ID2
    AND LCKR.REQUEST = 0
    AND WT.LMODE = 0
    AND WT.type='TX';

RESOLVING LOCKS IN ORACLE

As per Oracle, the blocked (or waiting) session will continue to wait until

• Blocking session issues a COMMIT
• Blocking session issues a ROLLBACK
• Blocking session disconnects from the database