Identifying and Releasing Locks on objects

First identify the object id that is holding the lock. Query dba_locks,v$lock, v$lock_object to get the SID and object_id

SQL> select * from dba_locks
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED

LOCK_ID1
---------- -------------------------- ---------------------------------------- ---------------------
145 DML Row-X (SX) None

51966

SQL> select * from v$lock;


SQL> select sid, username from v$session;
SID USERNAME
------ ------------------
143
145 SCOTT
146 SCOTT


SQL> select session_id, process, locked_mode from v$locked_object where object_id=51966;

SESSION_ID PROCESS LOCKED_MODE
---------- ------------ -----------
145 1712:5368 3


SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
51966
OR

SQL> select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id;

OS_USER_NAME LOCKED_MODE OBJECT_NAME
------------------------------ ----------- ---------------------------------------------------------
SCOTT 3 SAMPLE

Also identify the serial# of the sid that is holding the lock.
SQL> select sid, serial#, username from v$session;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
143 38
145 327 SCOTT
146 237 SCOTT
147 1886
154 1
158 4
160 1
161 1
162 1
163 1
164 1

SID SERIAL# USERNAME
---------- ---------- ------------------------------
165 1
166 1
167 1
168 1
169 1
170 1

17 rows selected.


Then, using the sid and serial#, you can kill the session to release the locks. Kill the session only when user is unavailable or in case of emergency. Or you can request the user to commit or rollback to release the locks.

SQL> alter system kill session '146, 237';

System altered.

Note that you cannot kill your own session.
SQL> alter system kill session '145, 327';
alter system kill session '145, 327'
*
ERROR at line 1:
ORA-00027: cannot kill current session