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

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 2 Jul 2009 10:12:33 -0700
Message-ID: <bf46380907021012k78d3d97o6bab4cfdeb5fe44b_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 02 2009 - 12:12:33 CDT