Menu
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!
607
1) Bouncing the system can release the lock. 2) Using the v$session, v$lock and dba_objects and manually you could clear them up. 3) In case you are using 10g, you could use @lock to find out which block has the lock and @lock_ob to find out which user has locked the table.
Type "Kill <session ID>" into the command prompt, and press "Enter." Replace "Session ID" with the session ID number you wrote down in Step 2. This kills the user's session and the SQL lock that was created.
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#'; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
