如果正常情况下,会自动解锁的,如果出现了死锁, 就需要kill session. 1. 查看哪些session锁: SQL语句:select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1); SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1); 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';' -------------------------------------------------------------------------------- alter system kill session '132,731'; alter system kill session '275,15205'; alter system kill session '308,206'; alter system kill session '407,3510'; oracle 锁问题的解决 http://blog.csdn.net/tianlesoftware/archive/2009/10/28/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
执行 select b.owner,b.object_name,a.session_id,a.locked_mode,c.serial#,c.sid||','||c.serial# from v$locked_object a,dba_objects b ,v$session c where b.object_id = a.object_id And a.session_id = c.sid 查看被锁表的会话id, 执行 alter system kill session 'sid,serial#' immediate; 选中你要解锁的表 sid serial#,杀掉被锁表进程中的会话 你也在可以是企业管理器中操作,“工具”----“Diagnostic Pack”-----"Lock Monitor"选项,打开了“锁管理器”窗口,点你要解锁的表,然后在菜单栏中选择“下钻”,点终止会话的
支持,补充一点是alter system kill session必须要求用户有alter system的权限才可以
1.select * from v$locked_object; --找到session_id2.select * from v$session t where t.SID='你查到的Session_id'; --找到这个Session的SERIAL#3.alter system kill session '你查到的Session_id,你查到的SERIAL#';
select b.sid,b.machine,c.object_name,a.oracle_username, a.locked_mode,b.osuser, 'alter system kill session '''||b.sid||','||b.serial#||''';' from v$locked_object a,v$session b,all_objects c where a.session_id=b.sid and a.object_id=c.object_id
select t2.sid,SERIAL#,t3.SPID,STATUS,EVENT,STATE,t1.OBJECT from v$access t1 join v$session t2 on t1.SID=t2.SID join v$process t3 on t2.PADDR=t3.ADDR where t1.object in ('SY_CLIENT','SY_FUNDACCOUNT','SY_FSJOUR','SY_FUND','SY_STOCK');select t2.sid,SERIAL#,t3.SPID,STATUS,EVENT,STATE,t1.OBJECT from v$access t1 join v$session t2 on t1.SID=t2.SID join v$process t3 on t2.PADDR=t3.ADDR where t1.object in ('TB_DJ_CLIENT1','TB_DJ_FUNDACCOUNT1','TB_DJ_FSJOUR1','TB_DJ_FUND1','TB_DJ_STOCK1');
如果正常情况下,会自动解锁的,如果出现了死锁, 就需要kill session. 1. 查看哪些session锁:
SQL语句:select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1); SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '132,731';
alter system kill session '275,15205';
alter system kill session '308,206';
alter system kill session '407,3510';
oracle 锁问题的解决
http://blog.csdn.net/tianlesoftware/archive/2009/10/28/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
select b.owner,b.object_name,a.session_id,a.locked_mode,c.serial#,c.sid||','||c.serial#
from v$locked_object a,dba_objects b ,v$session c
where b.object_id = a.object_id
And a.session_id = c.sid
查看被锁表的会话id,
执行 alter system kill session 'sid,serial#' immediate; 选中你要解锁的表 sid serial#,杀掉被锁表进程中的会话 你也在可以是企业管理器中操作,“工具”----“Diagnostic Pack”-----"Lock Monitor"选项,打开了“锁管理器”窗口,点你要解锁的表,然后在菜单栏中选择“下钻”,点终止会话的
支持,补充一点是alter system kill session必须要求用户有alter system的权限才可以
如果你能找到这个连接,在上面rollback或commit就行了。
where t.SID='你查到的Session_id'; --找到这个Session的SERIAL#3.alter system kill session '你查到的Session_id,你查到的SERIAL#';
a.locked_mode,b.osuser,
'alter system kill session '''||b.sid||','||b.serial#||''';'
from v$locked_object a,v$session b,all_objects c
where a.session_id=b.sid and a.object_id=c.object_id
宁可错杀3000,不可放过一个
select t2.sid,SERIAL#,t3.SPID,STATUS,EVENT,STATE,t1.OBJECT from v$access t1
join v$session t2
on t1.SID=t2.SID
join v$process t3
on t2.PADDR=t3.ADDR
where t1.object in ('SY_CLIENT','SY_FUNDACCOUNT','SY_FSJOUR','SY_FUND','SY_STOCK');select t2.sid,SERIAL#,t3.SPID,STATUS,EVENT,STATE,t1.OBJECT from v$access t1
join v$session t2
on t1.SID=t2.SID
join v$process t3
on t2.PADDR=t3.ADDR
where t1.object in ('TB_DJ_CLIENT1','TB_DJ_FUNDACCOUNT1','TB_DJ_FSJOUR1','TB_DJ_FUND1','TB_DJ_STOCK1');