Oracle锁处理、解锁方法
网络编程 2021-07-05 16:09www.168986.cn编程入门
本文通过实例代码较详细的给大家介绍了Oracle锁处理、解锁方法,需要的朋友可以参考下
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 a20 col lock_type for a15 col mode_requested for a10 col mode_held for a10 col lock_id1 for a10 col lock_id2 for a10 set linesize 120 set pagesize 999 with 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', / Mon Lock equivalent / 1, 'Null', / N / 2, 'Row-S (SS)', / L / 3, 'Row-X (SX)', / R / 4, 'Share', / S / 5, 'S/Row-X (SSX)', / C / 6, 'Exclusive', / X / TO_CHAR (lmode)) mode_held, DECODE (request, 0, 'None', / Mon Lock equivalent / 1, 'Null', / N / 2, 'Row-S (SS)', / L / 3, 'Row-X (SX)', / R / 4, 'Share', / S / 5, 'S/Row-X (SSX)', / C / 6, 'Exclusive', / X / TO_CHAR (request)) mode_requested, TO_CHAR (id1) lock_id1, TO_CHAR (id2) lock_id2, ctime last_convert, DECODE (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 (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_holders connect by prior waiting_session = holding_session start with holding_session is null;
以上所述是长沙网络推广给大家介绍的Oracle锁处理、解锁方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,长沙网络推广会及时回复大家的。在此也非常感谢大家对狼蚁SEO网站的支持!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南