| How to unlock table partition [message #148376] |
Wed, 23 November 2005 20:02  |
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  |
rleishman
Messages: 2845
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 |
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 |
rleishman
Messages: 2845
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 #315882 is a reply to message #315873 ] |
Wed, 23 April 2008 02:16 |
Frank
Messages: 6225
Registered: April 2002
Location: NL |
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 |
rleishman
Messages: 2845
Registered: October 2005
Location: Melbourne, Australia |
Senior Member |
|
|
|
2 and a half years. That's gotta be a new record.
|
|
|
]
Members
Search
Help
Register
Login
Home
