据库锁,诊断锁

1、查看数据库锁,诊断锁的来源及类型：

select object_id,session_id,locked_mode from v$locked_object;

或者用以下命令：

select b.owner,b.object_name,l.session_id,l.locked_mode

from v$locked_object l, dba_objects b

where b.object_id=l.object_id

SELECT lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

o.owner,o.object_name,o.object_type,s.sid,s.serial#

FROM v$locked_object l,dba_objects o,v$session s

WHERE l.object_id=o.object_id

AND l.session_id=s.sid

ORDER BY o.object_id,xidusn DESC

2、找出数据库的serial#,以备杀死：

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

3、杀死该session

alter system kill session 'sid,serial#'

用步骤2中查出来的记录，对应进该语句删除

就是这样子，以下是我操作的方法：

第一步：（只是用于查看哪些表被锁住，真正有用的是第二、第三步）

select b.owner,b.object_name,l.session_id,l.locked_mode

from v$locked_object l, dba_objects b

where b.object_id=l.object_id

后：

OWNEROBJECT_NAMESESSION_IDLOCKED_MODE

1BSZCGLTDISPOSE_ACCEPT_F11153

2BSZCGLTDISPOSE_ACCEPT_F10973

3BSZCGLTDISPOSE_ACCEPT_Z11163

4BSZCGLTDISPOSE_ACCEPT_Z11113

5BSZCGLTDISPOSE_ACCEPT_Z11033

6BSZCGLTDISPOSE_ACCEPT_Z11003

7BSZCGLTDISPOSE_ACCEPT_Z10973

8BSZCGLTDISPOSE_ACCEPT_Z10923

9BSZCGLTDISPOSE_DAMAGE_Z11063

10BSZCGLTZC6_22CL10973

可以看出，那些表被锁住

第二步：

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

后：

USERNAME SIDSERIAL#LOGON_TIME

1BSZCGL11151322011-12-6 14:51:35

2BSZCGL10971162011-12-6 14:51:57

3BSZCGL10971162011-12-6 14:51:57

4BSZCGL10971162011-12-6 14:51:57

5BSZCGL11111552011-12-6 14:56:29

6BSZCGL11032922011-12-6 14:57:34

7BSZCGL11163882011-12-6 15:04:56

8BSZCGL11002402011-12-6 15:08:13

9BSZCGL11062282011-12-6 15:26:20

10BSZCGL1092102011-12-6 15:26:46

第三步：（关键）

执行：（alter system kill session 'sid,serial#'）具体如下：

alter system kill session '1115,132'

alter system kill session '1097,116'

alter system kill session '1111,155'

alter system kill session '1103,292'

alter system kill session '1116,388'

alter system kill session '1100,240'

alter system kill session '1106,228'

alter system kill session '1092,10'

执行成功，会提示执行完毕！

有可能执行完一条之后，其他的ID也跟着消失，执行完3后，在执行2，检查时候清除完毕