oracle数据中报错"记录被另一个用户锁住"_数据库oracle报错1097-CSDN博客

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_usernameUser_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

后：

OWNER OBJECT_NAME SESSION_ID LOCKED_MODE

1  BSZCGL TDISPOSE_ACCEPT_F   1115        3
2  BSZCGL TDISPOSE_ACCEPT_F   1097        3
3   BSZCGL TDISPOSE_ACCEPT_Z   1116        3
4  BSZCGL TDISPOSE_ACCEPT_Z    1111        3
5  BSZCGL TDISPOSE_ACCEPT_Z   1103        3
6  BSZCGL TDISPOSE_ACCEPT_Z   1100        3
7  BSZCGL TDISPOSE_ACCEPT_Z   1097        3
8  BSZCGL TDISPOSE_ACCEPT_Z   1092        3
9   BSZCGL TDISPOSE_DAMAGE_Z   1106        3
10  BSZCGL TZC6_22CL           1097        3

可以看出，那些表被锁住

第二步：

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 SID SERIAL# LOGON_TIME

1 BSZCGL  1115  132   2011-12-6 14:51:35
2 BSZCGL  1097 116  2011-12-6 14:51:57
3 BSZCGL  1097  116   2011-12-6 14:51:57
4 BSZCGL  1097  116   2011-12-614:51:57
5 BSZCGL  1111  155   2011-12-6 14:56:29
6 BSZCGL  1103   292  2011-12-6 14:57:34
7 BSZCGL  1116  388   2011-12-6 15:04:56
8 BSZCGL  1100  240   2011-12-6 15:08:13
9 BSZCGL  1106  228   2011-12-6 15:26:20
10 BSZCGL 1092  10   2011-12-6 15:26:46