prompt
prompt List all process which are in lock-wait, the type of lock requested
prompt and the process that is holding the lock queue
prompt
column username format a15
column sid format A12
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 999990
column id2 format 999990
break on id1 skip 1 dup
prompt
set pagesize 120
set linesize 120
select sn.username,
sn.sid||','||sn.serial# sid,
m.type,
decode(m.lmode,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
ltrim(to_char(m.lmode,'990'))) lmode,
decode(m.request,
0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
ltrim(to_char(m.request,'990'))) request,
m.id1,
m.id2,
n.ctime,
sn.status
from v$session sn,
v$lock m,
(select id1,id2,max(ctime) ctime
from v$lock
where request=0
group by id1,id2) n
where sn.sid=m.sid and
m.id1=n.id1 and
m.id2=n.id2 and
(m.request!=0 or m.request!=4) and
(m.id1,m.id2) in (
select s.id1,s.id2
from v$lock s
where request!=0 )
order by n.ctime,id1,id2,m.request;
clear breaks
prompt
prompt Tables with exclusive acess
prompt
col BLOCKING_OTHERS format a15
col sid format 999999
select
SESSION_ID sid,
NAME,
MODE_HELD,
MODE_REQUESTED,
BLOCKING_OTHERS
from sys.dba_dml_locks
order by session_id;