Home » SQL & PL/SQL » SQL & PL/SQL » How to unlock table partition
How to unlock table partition [message #148376] Wed, 23 November 2005 20:02 Go to next message
war123
Messages: 8
Registered: November 2005
Junior Member
Dear

I have a table that have lock by Oracle

Insert into TempTable(10); --Lock table until call commit.
Commit; --Unlock table
-> It will unlock the table;

Now I want to unlock the table without using Commit statement.
How can I do it?

Re: How to unlock table partition [message #148384 is a reply to message #148376] Wed, 23 November 2005 21:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can ROLLBACK instead of COMMIT, but then you lose your changes.

You cannot manually release a system initiated lock except via COMMIT and ROLLBACK;

You can release user-initiated locks though. These locks are created and released using the DBMS_LOCK package.

To recap:
- Locks created by INSERT, UPDATE, DELETE, LOCK, ALTER, etc are released ONLY on COMMIT or ROLLBACK
- Locks created manually by DBMS_LOCK are released by DBMS_LOCK and may also be configured to be released by COMMIT or ROLLBACK.

_____________
Ross Leishman
Re: How to unlock table partition [message #148445 is a reply to message #148376] Thu, 24 November 2005 04:57 Go to previous messageGo to next message
war123
Messages: 8
Registered: November 2005
Junior Member
Thanks rleishman,

I have search for using DBMS_LOCK but I didn't find any way to use it.
Can you give the example of DBMS_LOCK.

Re: How to unlock table partition [message #148518 is a reply to message #148445] Thu, 24 November 2005 16:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I mean this in the nicest possible way (ie. no offense intended) - if you need help to understand DBMS_LOCK, you definitely should not be using it. Look at the documentation for DBMS_LOCK - it comes with all sorts of warnings to DBAs that it should only be granted to senior/experienced/approved personnel because it has the potential to bring a database to its knees.

Instead of chasing improbable solutions, just tell us the nature of the original problem that took you down the path of trying to unlock a table without a commit/rollback.

It is very unlikely that you are trying to do something that has not been done before - someone here will be able to help.
_____________
Ross Leishman
Re: How to unlock table partition [message #315873 is a reply to message #148376] Wed, 23 April 2008 01:52 Go to previous messageGo to next message
pradyumnah
Messages: 1
Registered: November 2007
Location: india
Junior Member
kill the on going session using the view v$session...hope it works.. Confused
Re: How to unlock table partition [message #315882 is a reply to message #315873] Wed, 23 April 2008 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't wake the zombies
Re: How to unlock table partition [message #315896 is a reply to message #315882] Wed, 23 April 2008 02:50 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
2 and a half years. That's gotta be a new record.
Previous Topic: Next Upper value
Next Topic: ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation
Goto Forum:
  


Current Time: Thu Dec 08 02:03:08 CST 2016

Total time taken to generate the page: 0.06921 seconds