How to release a locked object

[patni]

HI,

I was doing an update and since it was taking a long time I stopped it but the session was not released, so I killed the session.
I could see in v$locked_objects that the table is still locked.

How do I release the lock.
Any help is appreciated..

- Patni

[Sekhar]

Is this in production environment or in Devlopment.

If its in Dev - rename the table with other name and again revert it to its original - Just check all the indexes and constraints before doing this

[raghud]

Locks are Session based. If you kill that session you will be fine. It may take some while for Oracle to do rollback operation.

[gandolf989]

Are you killing the session from the os?
If so check v$session to see if the session is still open.

You can of course kill a session from oracle.
When you kill the session from Oracle, Oracle still has to
rollback any uncommitted changes from the session. It is
probable that the lock will remain until the rollback is complete.

SELECT SID,SERIAL#,STATUS,SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';

SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 INACTIVE DEDICATED
12 63 INACTIVE DEDICATED
2 rows selected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';

SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 KILLED PSEUDO
12 63 INACTIVE DEDICATED
2 rows selected.

[patni]

Hi,

I killed the session and the query to V$Session returned Status as KILLED, but still the table was locked.

I had to just wait till ROLLBACK. I was just wondering if there is any way at user level to Force ROLLBACK or release the lock instantly.

- Patni

KILLED

[gandolf989]

I'm not aware of a way that you can immediately release the lock for a session that is disconnected and rolling back.

Think of it like this, if you need a lock on a table for a transaction, is it fair to say that you need the lock until the transaction either is finished committing or finished rolling back?

If you tune the transaction to make it run faster, then when it releases the lock isn't as critical.

You should run the transaction with sql_trace and timed _statistics turned on and then run tkprof on the trace file to get an idea of what is happening in your transaction. You can also get good information from statspack.

[patni]

Thanks..I get your point..
Will try tracing it next time.

- Patni