Re: Breaking a Lock

From: Jim Anderson <anderson_at_ci.lincoln.ne.us> >> Is there any way to break a lock? I disconnected a session that had a lock on a table. The session showed up as "Killed", but the lock remained. <<

I have seen where the front-end connection process to Oracle is terminated but the back-end process that does all the work does not terminate. This type of situation can cause your problem, plus I have seen a lock held where neither the front or back-end processes existed. It took about 20 minutes for Oracle to detect this and clean it up.

Try the following:
1) Always locate the Oracle back-end process before terminating so that you can verify it is really gone. Allow some time for rollback to occur; Oracle normally uses the existing back-end process to do this, but if the process does not disappear in five minutes then kill it at the OS level. 2) Check your sqlnet ver 2 parameter file for the connection timeout parameter. I do not remember its name, but there is a dead connection locate and kill parameter that you should have set. It is in the manual.

If the back-end process is gone and the lock is still held past the time set for sqlnet to detect dead sessions then the only way I know to free the lock is to bounce the instance.

Hope this helps.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Mon Mar 16 1998 - 00:00:00 CST