oracle如何避免表锁定,Oracle 解决表锁定的问题
1. 通过v$session，v$locked_object查看谁锁定了资源
select t1.sid, t1.serial#, t1.username, t1.logon_time
fromv$session t1 , v$locked_object t2
where t1.sid = t2.session_id
order byt1.logon_time
more：
select s.terminal,
s.machine,
s.program,
sid,
s.serial#,
a.oracle_username,
a.process,
o.owner,
o.object_id,
o.object_name,
a.locked_mode,
DECODE(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Execlusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type
from v$session s, v$locked_object a, dba_objects o
where s.sid = a.session_id
and o.object_id = a.object_id;
2. 通过alter system kill session 'sid, serial#'把session kill掉
alter system kill session '6,8'
=================================================================================
SELECT RPAD (oracle_username, 10) o_name, session_id SID,
DECODE (locked_mode,
0, 'None',
1, 'Null',
2, 'Row share',
3, 'Row Execlusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive'
) lock_type,
object_name, xidusn, xidslot, xidsqn
FROM v$locked_object, all_objects
WHERE v$locked_object.object_id = all_objects.object_id;
select s.terminal,s.machine,s.program,sid,s.serial#,
a.oracle_username, a.process, o.owner, o.object_id, o.object_name, a.locked_mode
from v$session s, v$locked_object a, dba_objects o
where s.sid=a.session_id
and o.object_id=a.object_id
SELECT SID, DECODE (BLOCK, 0, 'NO', 'YES') blocker,
DECODE (request, 0, 'NO', 'YES') waiter
FROM v$lock
WHERE request > 0 OR BLOCK > 0
ORDER BY BLOCK DESC;
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
A.查哪个过程被锁
查V$DB_OBJECT_CACHE视图: select * from v$db_object_cache where owner='可疑用户' and locks!=0; -->以确定过程名;
B.查是哪个SID
查V$ACCESS视图: select * from v$access where owner='该用户' and object='确定的过程名';
C.查SID & SERIAL#
查V$SESSION视图: select * from v$session where sid='B中查到的ID号'; -->记录paddr
查V$PROCESS视图: select * from v$process where addr='上步中查到的PADDR'; -->记录SPID
D.杀进程
先杀Oracle进程: alter system kill session '在C中确定的ID,在C中确定的SERIAL#';
再杀操作系统进程: kill -9 SPID & ORAKILL C中确定的SID C中确定的SPID
创作挑战赛新人创作奖励来咯，坚持创作打卡瓜分现金大奖总结
以上是生活随笔为你收集整理的oracle如何避免表锁定,Oracle 解决表锁定的问题的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： oracle 定时清理表数据,关于Ora
- 下一篇： oracle指定源位置怎么弄,ORACL