锁的事情说一说-凯发app官方网站

必备技能，对于锁平时不阻塞也要排查的话，那就先乱查一通
--当前全部锁
set lin 200 pages 500
col username for a22
col object_name for a22
select a.sid,b.serial#,decode(a.type,'tm',c.object_name,null) object_name,a.type,b.username,
decode(a.lmode,
0,'0:none',
1,'1:null',
2,'2:ss(row-share)',
3,'3:sx(row-x)',
4,'4:s(share)',
5,'5:ssx(s/row-x)',
6,'6:x(exclusive)') lmode,
decode(a.request,
0,'0:none',
1,'1:null',
2,'2:ss(row-share)',
3,'3:sx(row-x)',
4,'4:s(share)',
5,'5:ssx(s/row-x)',
6,'6:x(exclusive)') request,
a.ctime,a.block
from v$lock a,v$session b ,dba_objects c where b.sid=a.sid and c.object_id()=a.id1;
col type for a4
col name for a25
col description for a60
select type,name,description from v$lock_type where type=upper('&type');
col oracle_username for a22
col os_user_name for a22
select xidusn,object_id,session_id,oracle_username,os_user_name,process from v$locked_object;
col object_name for a22
select d.object_id, substr(object_name,1,20) object_name, l.session_id, l.oracle_username, l.locked_mode,xidusn,xidslot,xidsqn from v$locked_object l, dba_objects d where d.object_id=l.object_id;
col username for a22
col machine for a25
col program for a35
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);
col sql_text for a60
select sql_id,sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
select waiting_session, holding_session, lock_type, mode_held from dba_waiters; --慎用，可能返回很多

lmode类型说明：

阅读(126) | 评论(0) | 转发(0) |

上一篇：用dg迁移win上的11g到19c

下一篇：srvctl add service 报错 prcr-1006 prcd-1302

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们