Cacher is the code snippet organizer for pro developers

-- 1 - Identify lock
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from 
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, 
(SELECT OBJECT_ID, OWNER, OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS) B
where a.object_id=b.object_id;

-- 2 - Identify which session is locking
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       P.SPID,
       s.username, S.OSUSER,
       s.program
FROM   gv$session s
       JOIN GV$PROCESS P ON P.ADDR = S.PADDR AND P.INST_ID = S.INST_ID
WHERE  s.type != 'BACKGROUND' and s.sid = ?;

-- 3 - kill the session 
ALTER SYSTEM KILL SESSION '168,770';