oracle数据库查询锁表与解锁语句

作者:袖梨 2022-06-29

查询锁表语句

 代码如下 复制代码

SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
decode(D.type,
‘XR’,
‘NULL’,
‘RS’,
‘SS(Row-S)’,
‘CF’,
‘SS(Row-S)’,
‘TM’,
‘TABLE LOCK’,
‘PW’,
‘TABLE LOCK’,
‘TO’,
‘TABLE LOCK’,
‘TS’,
‘TABLE LOCK’,
‘RT’,
‘ROW LOCK’,
‘TX’,
‘ROW LOCK’,
‘MR’,
‘S(Share)’,
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id,
DECODE(C.STATUS, ‘INACTIVE’, ‘不活动’, ‘ACTIVE’, ‘活动’) 活动情况,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE
ORDER BY 1, 2;

例2

查询锁定的表:

 代码如下 复制代码

  SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,

  l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time

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

  WHERE l.object_id = o.object_id

  AND l.session_id = s.sid

  ORDER BY sid, s.serial# ;

 

然后删除掉系统锁定的此记录

例1

 代码如下 复制代码

ALTER system KILL session 'SID,serial#';

如果有多个sid 和serial# 重复删除每条记录

相关文章

精彩推荐