Re: How do I list all materialized views in a refresh group?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Roger.Xu@xxxxxxxx
- Date: Thu, 2 Jul 2009 10:12:33 -0700
On Wed, Jul 1, 2009 at 12:13 PM, Xu, Roger <Roger.Xu@xxxxxxxx> wrote:
> There are dbms_refresh.add, dbms_refresh.subtract, etc. How come there
> is no dbms_refresh.list to list all the mviews in a refresh group?
> Thanks in advance. Roger
>
>
This query may be useful.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
=========================================
col owner format a15 head 'SNAPSHOT|OWNER'
col rname format a12 head 'REFRESH|GROUP'
col refresh_mode format a8 head 'REFRESH|MODE'
col name format a30 head 'SNAPSHOT'
col last_refresh format a20 head 'LAST REFRESH'
col next_refresh format a20 head 'NEXT REFRESH'
break on owner skip on refresh_mode on rname
set line 110
select
s.owner owner
,s.refresh_mode
--, decode(r.rname, null, '-NO REFRESH-',rname,
decode(s.refresh_mode,'COMMIT','NA'), 'UNKOWN')
-- , decode(r.rname, null,decode(s.refresh_mode,'COMMIT','NA','-NO
REFRESH-'), r.rname) rname
, case
when r.rname is null and s.refresh_mode = 'COMMIT' then 'NA'
when r.rname is not null then r.rname
else 'UNKOWN'
end rname
,s.name name
,to_char(s.last_refresh,'mm/dd/yyyy hh24:mi:ss' ) last_refresh
,decode( r.next_date,
null, s.next,
to_char(r.next_date,'mm/dd/yyyy hh24:mi:ss')
) next_refresh
from all_snapshots s, all_refresh r
where s.refresh_group = r.refgroup(+)
order by owner, refresh_mode, rname, name
/
Other related posts:
