oracle 表锁死的解决方法

oracle 表锁死的解决方法

我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在pl/sqlDeveloper工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就更难了，下面这叫查询语句可以查询出所以被锁的会话。如下:

1、查看被锁的表：

SELECTp.spid,       c.object_name,       b.session_id,       a.serial#,       b.oracle_username,       b.os_user_name  FROMv$process p, v$session a,v$locked_object b, all_objects c  WHEREp.addr = a.paddr   ANDa.process = b.process   ANDc.object_id = b.object_id;

解锁：
alter system kill session'b.session_id,a.serial#';
或在系统层面终止进程：
[Windows]  ntsd -c q -pp.spid
[Linux]       ps-ef|grepp.spid

2、查询当前表锁的方法：

SELECTall_objects.object_name,       s.sid,       s.serial#,       s.osuser,       s.program,       s.machine,       s.client_info  FROMv$lock k, v$session s,all_objects  WHEREk.sid = s.sid   ANDk.TYPEIN('TX','TM')   ANDk.id1 =all_objects.object_id;

v$locked_object：用以详细的描述了当前锁定对象的详细信息，OBJECT_ID为对象ID，SESSION_ID为当前登录用户Session号，ORACLE_USERNAME为Oracle的用户名，OS_USER_NAME为操作系统用户名等

v$lock：该视图说明当前锁定的所有对象，锁定SID号，锁定类型等信息；

3、查询锁

--存在锁请求，即被阻塞 SELECTsn.username,       m.sid,       sn.serial#,       m.TYPE,       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  FROMv$session sn, v$lock m  WHEREsn.sid = m.sid   ANDm.request !=0  ORDERBYid1, id2, m.request --不存在锁请求，但是锁定的对象被其他会话请求锁定 SELECTsn.username,       m.sid,       sn.serial#,       m.TYPE,       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  FROMv$session sn, v$lock m  WHEREsn.sid = m.sid   ANDm.request =0   ANDm.lmode !=4   AND(id1, id2)IN(SELECTs.id1, s.id2                        FROMv$lock s                       WHERErequest !=0                         ANDs.id1 = m.id1                         ANDs.id2 = m.id2)  ORDERBYid1, id2, m.request

3、查询锁

--存在锁请求，即被阻塞 SELECTsn.username,       m.sid,       sn.serial#,       m.TYPE,       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  FROMv$session sn, v$lock m  WHEREsn.sid = m.sid   ANDm.request !=0  ORDERBYid1, id2, m.request --不存在锁请求，但是锁定的对象被其他会话请求锁定 SELECTsn.username,       m.sid,       sn.serial#,       m.TYPE,       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  FROMv$session sn, v$lock m  WHEREsn.sid = m.sid   ANDm.request =0   ANDm.lmode !=4   AND(id1, id2)IN(SELECTs.id1, s.id2                        FROMv$lock s                       WHERErequest !=0                         ANDs.id1 = m.id1                         ANDs.id2 = m.id2)  ORDERBYid1, id2, m.request