Re: How do I list all materialized views in a refresh group?

From: Jared Still <>
Date: Thu, 2 Jul 2009 10:12:33 -0700
Message-ID: <>

On Wed, Jul 1, 2009 at 12:13 PM, Xu, Roger <> 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


   s.owner owner
   --, 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 , name ,to_char(s.last_refresh,'mm/dd/yyyy hh24:mi:ss' ) last_refresh ,decode( r.next_date, null,, 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 /
Received on Thu Jul 02 2009 - 12:12:33 CDT

Original text of this message