解锁可以通过commit or rollback来解除。
如果你加的是EXCLUSIVE 锁,下一次是加不上了的
如果你加的是EXCLUSIVE 锁,下一次是加不上了的
1.
查找系统中谁在锁表
(
有很多种方法
):
a
)
.
查找进程号和序列号
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;
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;
b ) . 查找进程号和序列号
set echo OFF rem
该脚本是用来检验系统中谁在锁表
column username format a13
column object_name format a10
column osuser format a10
column MACHINE format a10
column LockType format a10
select A.sid,b.serial#,
decode(A.type,'MR','Media Recovery','RT','Redo Thread',
'UN','User Name','TX','Transaction','TM','DML','UL','PL/SQL User Lock', 'DX','Distributed Xaction',
'CF','Control File','IS','Instance State','FS','File Set', 'IR', 'Instance Recovery', 'ST',
'Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion',
'LS','Log Start or Switch','RW','Row Wait','SQ', 'Sequence Number', 'TE',
'Extend Table','TT','Temp Table','Unknown')
column username format a13
column object_name format a10
column osuser format a10
column MACHINE format a10
column LockType format a10
select A.sid,b.serial#,
decode(A.type,'MR','Media Recovery','RT','Redo Thread',
'UN','User Name','TX','Transaction','TM','DML','UL','PL/SQL User Lock', 'DX','Distributed Xaction',
'CF','Control File','IS','Instance State','FS','File Set', 'IR', 'Instance Recovery', 'ST',
'Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion',
'LS','Log Start or Switch','RW','Row Wait','SQ', 'Sequence Number', 'TE',
'Extend Table','TT','Temp Table','Unknown')
LockType,c.object_name,
---b.username,
---b.osuser,
decode(a.lmode, 0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',
5,'S/Row-X',
6,'Exclusive','Unknown')
---b.username,
---b.osuser,
decode(a.lmode, 0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',
5,'S/Row-X',
6,'Exclusive','Unknown')
LockMode,
B.MACHINE,
D.SPID
from v$lock a, v$session b, all_objects c, V$PROCESS D
where a.sid = b.sid
and a.type in ('TM', 'TX')
and c.object_id = a.id1
and B.PADDR = D.ADDR
---order by username /
b ) . 查找进程号和序列号
B.MACHINE,
D.SPID
from v$lock a, v$session b, all_objects c, V$PROCESS D
where a.sid = b.sid
and a.type in ('TM', 'TX')
and c.object_id = a.id1
and B.PADDR = D.ADDR
---order by username /
b ) . 查找进程号和序列号
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;
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;
c
)。查找进程号和序列号
(
查询出所有被锁的会话
)
:
SELECT sn.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
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0) -- 存在锁请求,即被阻塞
OR ( sn.SID = m.SID -- 不存在锁请求,但是锁定的对象被其他会话请求锁定
AND m.request = 0
AND lmode != 4
AND (id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
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
FROM v$session sn, v$lock m
WHERE (sn.SID = m.SID AND m.request != 0) -- 存在锁请求,即被阻塞
OR ( sn.SID = m.SID -- 不存在锁请求,但是锁定的对象被其他会话请求锁定
AND m.request = 0
AND lmode != 4
AND (id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;
2.
杀进程
(
解锁
):
通过以上查询知道了
sid
和
SERIAL#
(即:进程号
,
序列号)就可以开杀了
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;