Removing Locks in Oracle 10

oracle, sql, sqlplus

Solution

This will exactly serve your purpose:

SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = <TABLE_NAME>;

Use the `SESSION_ID` to find the corresponding `SERIAL#` with this statement:

SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (
    SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = <TABLE_NAME>
);

Locate the offending tuples `SID, SERIAL#` and release it like this:

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

Problem

I have an issue when trying to update a column value in sqlplus. It basically wont complete and just hangs. I am wondering if there is anyway to remove locks in oracle by force? I've noticed a table called v$locked_object which does contain an onject ID relevant to the issue I am having. Hope I've explained this good enough. Thanks in advance for any help!

Original source