--set pause 'Press Enter to continue'
--set pause on
prompt
prompt
Prompt ================================================== =====================
prompt Running enq to check for enqueues - for all instances
prompt

clear columns

col INST_ID format 999
col sid format 99999
col p1text format a15
col p2text format a7
col p3text format a7
set linesize 80
--select sid,p1text,p1, p2text,p2,p3text,p3 from v$session_wait
select inst_id, sid, p1, p1text, p2, p2text, p3, p3text from gv$session_wait
where event like 'enqueue%'
order by p1, p2, p3, inst_id, sid;

prompt
prompt
Prompt ================================================== =====================
Prompt Running glock - for all instances
prompt

set linesize 100
set pagesize 66
col inst_id format 9999 heading "Inst|ID"
col s1 format 999999 heading " Waiting|SID"
col c1 format a18 trunc heading "Program Name "
col module format a22 trunc
col lmode format 99999
col request format 9999999
col type format a2

prompt
prompt Waiting for TX Enqueue where mode is Exclusive
prompt '-------------------------------------'
prompt

select l.inst_id, l.SID s1, program c1, module, l.TYPE, l.ID1, l.ID2
from gv$lock l,
gv$session s
where l.type like 'TX'
and l.REQUEST = 6
and l.inst_id = s.inst_id
and l.sid = s.sid
order by id1, l.inst_id, l.SID;

prompt
prompt
prompt Holding TX Enqueue
prompt '-------------------------------------'
prompt

col s1 format 999999 heading "Blocking|SID"

select l.inst_id, l.SID s1, program c1, module, l.TYPE, l.ID1, l.ID2
from gv$lock l,
gv$session s
where l.type like 'TX'
and l.LMODE = 6
and (l.ID1,l.ID2) in (select id1, id2 from gv$lock
where type like 'TX' and REQUEST =6)
and l.inst_id = s.inst_id
and l.sid = s.sid
order by id1;

prompt
prompt
Prompt ================================================== =====================
prompt checking lcl - library cache locks - for all instances

column USER format a14
column INST_ID format 999
column SID_SERIAL format a10
column "EVENT" format a79 wrap
set feedback on

select
a.inst_id,
a.sid || ','|| b.serial# "SID_SERIAL",
b.username || '('|| b.osuser || ')' "USER",
a.event || '=>'|| a.p1text || '=' || a.p1raw || ' ' ||
a.p2text || '=' || a.p2 || ' ' ||
a.p3text || '=' || a.p3 "EVENT"
from gv$session_wait a, gv$session b
where a.sid = b.sid
and a.inst_id = b.inst_id
and a.event like 'library%'
order by 4, 1, 2
;


--prompt
--prompt
--Prompt ================================================== =====================
--prompt checking dba_ddl_locks - instance specific

--select count(*), MODE_HELD, MODE_REQUESTED
-- from dba_ddl_locks
--where MODE_HELD <> NULL
--group by MODE_HELD, MODE_REQUESTED;

prompt
prompt
Prompt ================================================== =====================
prompt checking dba_ddl_locks requesting a lock - instance specific

col name format a33
col type format a20
col mode_held heading "Mode|Held"
col mode_requested heading "Mode|Requested"
set feedback on

select session_id SID,
name,
type,
MODE_HELD,
MODE_REQUESTED
from dba_ddl_locks
where MODE_REQUESTED != 'None';



prompt
prompt
Prompt ================================================== =====================
prompt checking latch free events - for all instances

col event format a11
col name format a20

SELECT w.inst_id, w.sid, w.event, SUM(w.p3) Sleeps, n.name
FROM GV$SESSION_WAIT w, GV$LATCHNAME n
WHERE w.event = 'latch free'
AND w.p2 = n.latch#
and w.inst_id = n.inst_id
group BY w.inst_id, w.sid, w.event, n.name;

clear columns
set linesize 80