會話1：

lock table emp in exclusive mode; 這是個在表上加了TM鎖。
在會話

點選(此處)摺疊或開啟

10:56:05 SQL> select sid from v$mystat where rownum < 2;

SID

----------

60

Elapsed: 00:00:00.01

10:56:18 SQL> update emp set ename ='YAO';

檢視等待時間

select a.inst_id,
a.sid,
a.event,
a.sql_id,
b.sql_text running_sql,
c.sql_in_session,
c.sql_id_in_v$sql,
c.sql_text blocking_sql,
a.blocking_session,
a.blocking_instance
from gv$session a,
(select sql_id, sql_text
from (select sql_id,
sql_text,
row_number() over(partition by sql_id order by sql_id) as rn
from gv$sql)
where rn = 1) b,
(select a.inst_id,
a.sid,
a.sql_id sql_in_session,
b.sql_id sql_id_in_v$sql,
b.sql_text
from gv$session a, gv$sql b
where a.inst_id = b.inst_id
and a.PREV_EXEC_START =b.LAST_ACTIVE_TIME
and a.USERNAME = b.PARSING_SCHEMA_NAME
and a.MODULE = b.MODULE
) c
where a.sql_id = b.sql_id
and a.blocking_session is not null
and a.BLOCKING_SESSION = c.sid
and a.BLOCKING_INSTANCE = c.inst_id;
INST_ID SID EVENT SQL_ID RUNNING_SQL SQL_IN_SESSION SQL_ID_IN_V$SQL BLOCKING_SQL BLOCKING_SESSION BLOCKING_INSTANCE
----------- ------ --------------------------- -------------------- --------------------------- -------------- ---------------- -------------------------------- ---------------- -----------------
1 60 enq: TM - contention 108tdu434fydb update emp set ename ='YAO' 04kgt2wvy6b4g lock table emp in exclusive mode 1 1
1 38 enq: TM - contention c95cmb38801vk update emp set ename ='LOU' 04kgt2wvy6b4g lock table emp in exclusive mode 1 1

SQL> select a.sid,serial#,object_id,object_name,a.MACHINE,a.PROGRAM
from v$lock c,v$session a,dba_objects b
where c.type='TM'
and c.id1=b.object_id
and c.sid=a.sid;

SID SERIAL# OBJECT_ID OBJECT_NAME MACHINE PROGRAM
------------ ---------- ---------- ----------------- ---------------------- --------------------------
1 891 75335 EMP Test11g sqlplus@Test11g (TNS V1-V3)
60 317 75335 EMP Test11g sqlplus@Test11g (TNS V1-V3)
38 845 75335 EMP Test11g sqlplus@Test11g (TNS V1-V3)

enq:TM contention

相關文章