Tuesday, September 15, 2009

Which materialized views are being refreshed?

This is a handy little bit of SQL to see which materialized views are currently being refreshed.


select
o.owner "Owner"
,o.object_name "Mat View"
,s.username
,s.sid "Sid"
from
v$lock l
,dba_objects o
,v$session s
where o.object_id = l.id1
and l.type = 'JI'
and l.lmode = 6
and s.sid = l.sid
and o.object_type = 'TABLE';


The trick is in the lock type - JI.