Removing Locks in Oracle 10

sql oracle sqlplus

Thomas Pollock · Dec 12, 2012 · Viewed 97.5k times · Source

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!

Answer

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#';