时间:2021-05-24
1、查询锁情况
select sid,serial#,event,BLOCKING_SESSION from v$session where event like '%TX%';2、根据SID查询具体信息(可忽略)
select sid,serial#,username,machine,blocking_session from v$session where sid=<SID>;3、杀掉会话
#根据1和2中查到的SID和SERIAL# 定位会话,并杀掉
ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL>' IMMEDIATE;或
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>';附件:
#查询阻塞脚本
col waiting_session for a20col lock_type for a15col mode_requested for a10col mode_held for a10col lock_id1 for a10col lock_id2 for a10set linesize 120set pagesize 999with dba_locks_cust as (SELECT inst_id||'_'||sid session_id, DECODE (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', TYPE) lock_type, DECODE (lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lmode)) mode_held, DECODE (request, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (request)) mode_requested, TO_CHAR (id1) lock_id1, TO_CHAR (id2) lock_id2, ctime last_convert, DECODE (block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global', TO_CHAR (block)) blocking_others FROM gv$lock),lock_temp as(select * from dba_locks_cust),lock_holder as ( select w.session_id waiting_session, h.session_id holding_session, w.lock_type, h.mode_held, w.mode_requested, w.lock_id1, w.lock_id2 from lock_temp w, lock_temp h where h.blocking_others in ('Blocking','Global') and h.mode_held != 'None' and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2),lock_holders as(select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2 from lock_holder union all select holding_session, null, 'None', null, null, null, null from lock_holder minus select waiting_session, null, 'None', null, null, null, null from lock_holder )select lpad(' ',3*(level-1)) || waiting_session waiting_session, lock_type, mode_requested, mode_held, lock_id1, lock_id2 from lock_holdersconnect by prior waiting_session = holding_session start with holding_session is null;总结
以上所述是小编给大家介绍的Oracle锁处理、解锁方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
ps解锁图层锁的方法是:双击图层锁即可解锁图层。 AdobePhotoshop,简称“PS”,是由AdobeSystems开发和发行的图像处理软件。Photo
在Oracle中查询锁表及解锁:锁表查询的代码有以下的形式:selectcount(*)fromv$locked_object;select*fromv$loc
共享单车忘锁了处理的方法: 1、首先,点击进入共享单车的界面,看一下你的共享单车有没有提交,还是处于解锁状态; 2、如果处于解锁状态,那就跟着地图,找到那个
在DBA的日常工作中,经常遇到为Oracle用户解锁的操作;这篇文章给出在命令行下进行Oracle用户解锁的操作方法,通过几条简单的解锁语句就能完成此项工作。下
解决办法:ora-00031:sessionmarkedforkill处理oracle中杀不掉的锁一些ORACLE中的进程被杀掉后,状态被置为”killed”,