锁的事情说一说

必备技能，对于锁平时不阻塞也要排查的话，那就先乱查一通

--当前全部锁
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类型说明：