Check Locks and Kill the Sessions

These select statements will help identify the locking session so it can be killed.

NOTE: killing the session will cause immediate disconnect from the database, uncommitted data will be lost.

-- 1) show all locks

SQL> SELECT SS.SID, SS.SERIAL#,
OBJ.OBJECT_NAME,
MODULE,TO_CHAR(LOGON_TIME ,'DD-MON:HH24:MI')
FROM V$SESSION SS,
V$LOCKED_OBJECT LOBJ,
ALL_OBJECTS OBJ
WHERE LOBJ.OBJECT_ID= OBJ.OBJECT_ID
AND SS.SID= LOBJ.SESSION_ID
ORDER BY OBJ.OBJECT_NAME ;

-- 2) Identify locks on specific object

SQL> COLUMN OWNER FORMAT A20
COLUMN USERNAME FORMAT A20
COLUMN OBJECT_OWNER FORMAT A20
COLUMN OBJECT_NAME FORMAT A30
COLUMN LOCKED_MODE FORMAT A15
SELECT 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) LOCKED_MODE,
B.OS_USER_NAME
FROM DBA_OBJECTS A,
V$LOCKED_OBJECT B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.OBJECT_NAME = 'MTL_TXN_REQUEST_LINES' -- change table name as appropriate
ORDER BY 1, 2, 3, 4;

-- 3) Get session id (SID) and session serial number

SQL> SELECT L.SID,S.SERIAL#,S.USERNAME,S.PROGRAM,
DECODE(L.TYPE,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',L.TYPE||'System') RES,
T.NAME TAB,U.NAME OWNER,
L.ID1,L.ID2,
DECODE(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',NULL) LMODE,
DECODE(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',NULL) REQUEST
FROM V$LOCK L, V$SESSION S,
SYS.USER$ U,SYS.OBJ$ T
WHERE L.SID = S.SID
AND S.TYPE != 'BACKGROUND'
AND T.OBJ# = L.ID1
AND U.USER# = T.OWNER#
AND T.NAME LIKE 'MTL%';  -- change table name as appropriate
Use the above information to kill the lock associated with the stuck transaction.

In this specific example, it was the MO transaction (i.e. object = MTL_TXN_REQUEST_LINES)
Locate the SID & Serial then end/kill the lock from SQL*Plus 

SQL> alter system kill session 'SID, SERIAL';