最简单的办法是重新启动数据库或机器。 如果你不想这样,可以通过以下语句 select lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type from v$locked_object l,dba_objects o where l.object_id=o.object_id order by o.object_id desc select s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,s.terminal,s.machine,s.program,s.osuser from v$session s,v$lock l,dba_objects o where s.sid=l.sid and o.object_id=l.id1 and s.username is not null 查一查到底是谁锁表了,将那个session杀掉。如果还不行,可能就是你的表其中有块损坏了。
-- http://asktom.oracle.com/pls/ask/f?p=4950:12:1169808::NO::F4950_P12_DATE_YYYYMMDD:20020702#tag4515126525609 You AskedIn my PL/SQL code, when I select for update, if anybody else is working & has been locked what I want to update, I want my program to wait for n seconds for the row(s) to be released, if released then continue updating, if still not released after n seconds, detects the user who is locking the resource and informs me that person. I am writing pseudo code like this: select xxx for update yyy wait n seconds message (the user USER is locking the record RECORD)Thanks. -------------------------------------------------------------------------------- and we said...You cannot tell WHO is locking your recordYou can only tell WHO MIGHT BE (the entire set of people that might be).Oracle does not maintain an expensive list of all row locks in the system anywhere. That would inhibit greatly o scalability o performance o concurrency(as it does in SQLServer and DB2 and others). Locks are an attribute of the data, not stored in a serialized data structure as they do. (if you are interested in the details, you can look at my book -- i go into this i a large amount of depth) Now, your general logic can be implemented:declare resource_busy exception; pragma exception_init( resource_busy, -54 ); success boolean := False; begin for i in 1 .. 3 loop exit when (success); begin select xxx from yyy where .... for update NOWAIT; success := true; exception when resource_busy then dbms_lock.sleep(1); end; end loop; if ( not success ) then raise_application_error( -20001, 'row is locked by another session' ); end if;
2 SELECT * FROM V$LOCKED_OBJECT 看哪个SESSION锁了记录或表,
然后从 V$SESSION及V$PROCESS中找到那个进程,最后从OS中KILL
掉那个进程。接着就等待ORACLE的PMON自动清除死锁的记录。注意被杀错进程!!!
alter system kill session ...
正确的处理流程,你在进行所有数据库的更新操作以前,最好先执行一下
select ...from...where for update nowait.
如果这句没有触发异常,你再执行更新操作。这样就不会出现死机的现象了。
以上各位老兄所说的kill session的做法,不是通常所采用的,还有一点就是你的user不一定具备杀死会话的系统权限。
建议:把sqlnet.ora文件中添加下面的项目,db server就会每隔一段时间自动杀死死掉的会话了。sqlnet.expire_time = 10(分钟数)
如果在开发阶段,重启数据库没有关系;
如果在运行阶段,就应该去按上面所说去设置;如果需要立即处理,就去kill session吧,只要你有权限。
如果你不想这样,可以通过以下语句
select lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
order by o.object_id desc
select s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,s.terminal,s.machine,s.program,s.osuser
from v$session s,v$lock l,dba_objects o
where s.sid=l.sid
and o.object_id=l.id1
and s.username is not null
查一查到底是谁锁表了,将那个session杀掉。如果还不行,可能就是你的表其中有块损坏了。
http://asktom.oracle.com/pls/ask/f?p=4950:12:1169808::NO::F4950_P12_DATE_YYYYMMDD:20020702#tag4515126525609
You AskedIn my PL/SQL code, when I select for update, if anybody else is working & has
been locked what I want to update, I want my program to wait for n seconds for
the row(s) to be released, if released then continue updating, if still not
released after n seconds, detects the user who is locking the resource and
informs me that person. I am writing pseudo code like this: select xxx for update yyy
wait n seconds
message (the user USER is locking the record RECORD)Thanks.
--------------------------------------------------------------------------------
and we said...You cannot tell WHO is locking your recordYou can only tell WHO MIGHT BE (the entire set of people that might be).Oracle does not maintain an expensive list of all row locks in the system
anywhere. That would inhibit greatly o scalability
o performance
o concurrency(as it does in SQLServer and DB2 and others). Locks are an attribute of the
data, not stored in a serialized data structure as they do. (if you are
interested in the details, you can look at my book -- i go into this i a large
amount of depth)
Now, your general logic can be implemented:declare
resource_busy exception;
pragma exception_init( resource_busy, -54 );
success boolean := False;
begin for i in 1 .. 3
loop
exit when (success);
begin
select xxx from yyy where .... for update NOWAIT;
success := true;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop; if ( not success ) then
raise_application_error( -20001, 'row is locked by another session' );
end if;