各位大虾,有个急问题,帮帮忙呀!!
有个过程,一直无法编译,初步怀疑是过程中调用的表被锁了。
truncate 表时,提示ora-00054;编译过程时,提示ora-04021.
急!急!!急!!!
说明:查询了V$lock,dba_ddl_locks,v$locked_object都看不到被锁的对象
有个过程,一直无法编译,初步怀疑是过程中调用的表被锁了。
truncate 表时,提示ora-00054;编译过程时,提示ora-04021.
急!急!!急!!!
说明:查询了V$lock,dba_ddl_locks,v$locked_object都看不到被锁的对象
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) lmode,
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求,即被阻塞
OR ( sn.SID = m.SID --不存在锁请求,但是锁定的对象被其他会话请求锁定
AND m.request = 0
AND lmode != 4
AND (id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
alter system kill session 'sid,SERIAL#';
ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary. ORA-04021: timeout occurred while waiting to lock object stringstringstringstringstring
Cause: While waiting to lock a library object, a timeout is occurred.
Action: Retry the operation later.
可以参考一下我的blog:
oracle 锁问题的解决
http://blog.csdn.net/tianlesoftware/archive/2009/10/27/4733630.aspx
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
所以所有依赖于v$lock的SQL,以及后续的KILL SESSION的操作,都不会有什么效果的。
另外说明本ORACLE是10.2.0.4.0;数据库做了集群。