List Materialized Views In a Refresh Group

To show the members in a materialized view refresh group, we can use the following query that takes advantage of DBA_RGROUP and DBA_RCHILD. DBA_RGROUP includes all refresh groups. DBA_RCHILD contains all the children in any refresh group.

SQL>  select r.owner, r.name gp_name, c.name mv_name 
from DBA_RCHILD c, DBA_RGROUP r 
where c.owner=r.owner and c.REFGROUP=r.REFGROUP 
order by r.owner, r.name, c.name;

OWNER                          GP_NAME                        MV_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPS                     MV_GRP                         MV_SH_ABC
TEST_COMPS                     MV_GRP                         MV_SH_DEF
TEST_COMPS                     MV_GRP                         MV_SH_TEST