CODEWITHSUNDEEP
sqloracle-databasesqlplus
Thomas Pollock
Thomas Pollock

Reputation: 425

Removing Locks in Oracle 10

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!

Upvotes: 26

Views: 114395

Answers (3)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

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

Upvotes: 72

Thomas Pollock
Thomas Pollock

Reputation: 425

Managed to resolve this issue.

I looked in DBA_BLOCKERS table and got the session ID. Then killed the session and the lock was removed.

Upvotes: 1

Codo
Codo

Reputation: 78905

Probably, somebody else has updated the same table and hasn't committed yet. It might even be you (in a different tool or a different session).

The easiest way to get rid of the lock is to commit the work in the other session.

Upvotes: 0

Related Questions