| LOCKWAIT [message #362396] |
Tue, 02 December 2008 03:32  |
lioracle
Messages: 68
Registered: February 2008
Location: Israel
|
Member |
|
|
i run this command
"
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
2 and l1.id1=l2.id1
3 4 and l1.id2=l2.id2
5 6 /
BLOCKING
--------------------------------------------------------------------------------
SID 481 is blocking 492
481 = oracle@r3_mar (QMNC)
492 = oracle@r3_mar (CJQ0)
what that mean and how i release this?
thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: LOCKWAIT [message #362448 is a reply to message #362396] |
Tue, 02 December 2008 06:06  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your results mean the the Session identified by SID=481 has a lock on some resource, most likely one or more rows in a table.
The session identified by SID=491 wants to get a lock on the same resource (Probably wants to update/delete the rows)
The second session cannot proceed until the first session has released the lock.
This is usually done by the session finishing it's current transaction with either a commit or a rollback.
If neccessary, you can kill the first session, but this should only be done if there is not other way to get it to release the lock.
|
|
|
|
| Re: LOCKWAIT [message #362936 is a reply to message #362448] |
Thu, 04 December 2008 10:47 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
Can you explain me some points:-
1,How can I get the sql statement from blocking session from this.
2,If the session is owned by sys user then how can I commit or
rollback it.I would be thnakfull If you can elaborate on this
point
Regards,
Varun Punj,
|
|
|
|
|
|
| Re: LOCKWAIT [message #362954 is a reply to message #362950] |
Thu, 04 December 2008 11:56 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Thanks Michel.
Is this the right statement to get sql statement:-
select sql_text from v$sql where sql_id in( select prev_sql_id from v$session where sid='sid of blocking session')
Regarding the second point,You mean There is no way to commit or
rollback session of other users.We can only kill them.
Regards,
Varun Punj
|
|
|
|
| Re: LOCKWAIT [message #362958 is a reply to message #362954] |
Thu, 04 December 2008 12:26 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ sql_id or prev_sql_id in v$session depending on the case (active, inactive...)
2/ Yes
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
