DBA_REFRESH_CHILDREN lists all of the objects affected by every refresh group configured in the given database. [There are also views which are a bit more restricted: ALL_REFRESH_CHILDREN (listing all refresh groups and affected objects accessible by the connected user) and USER_REFRESH_CHILDREN (listing all refresh groups and affected objects owned by the connected user).] Of course it provides more information, such as the associated job number, the rollback/undo segment the group uses, the interval between refreshes and the date for the next refresh (among other details). The view description is as follows:
SQL> desc dba_refresh_children
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(30)
ROWNER NOT NULL VARCHAR2(30)
RNAME NOT NULL VARCHAR2(30)
REFGROUP NUMBER
IMPLICIT_DESTROY VARCHAR2(1)
PUSH_DEFERRED_RPC VARCHAR2(1)
REFRESH_AFTER_ERRORS VARCHAR2(1)
ROLLBACK_SEG VARCHAR2(30)
JOB NUMBER
NEXT_DATE DATE
INTERVAL VARCHAR2(200)
BROKEN VARCHAR2(1)
PURGE_OPTION NUMBER(38)
PARALLELISM NUMBER(38)
HEAP_SIZE NUMBER(38)
SQL>
The PUSH_DEFERRED_RPC column indicates, for updatable materialized views, whether or not to push any changes made to the snapshot data to the master table or master materialized view before the refresh begins. The valid values are Y and N, where Y indicates Oracle will push the changes from the snapshot to the master and N (the default) indicates Oracle will not.It's fairly straightforward to extract information from the view, as most of the columns names aren't ambigous; a generalized report might look like this:
SQL> select owner, name, type, refgroup, job, next_date, interval
2 from dba_refresh_children;
OWNER NAME TYPE REFGROUP JOB NEXT_DATE INTERVAL
---------- --------------------------- --------- --------- ---- ---------- --------------------------
NARBOW YARN_ORDS_PENDING_MV SNAPSHOT 13 95 30-SEP-08 TRUNC(SYSDATE + 1) + 4/24
NARBOW YARN_ORDS_BACKORD_MV SNAPSHOT 14 96 30-SEP-08 TRUNC(SYSDATE + 1) + 5/24
NARBOW DISCONTINUED_STOCK_NOS_MV SNAPSHOT 53 134 30-SEP-08 TRUNC(SYSDATE+1)+5/24
BORTUST RAW_MATL_BACKORD_MV SNAPSHOT 11 414 01-JAN-00 sysdate+365
SQL>
Job 414 in refresh group 11 is broken. It's broken because the next run date is Jan 1, 4000, the default date Oracle uses for jobs which shouldn't run:
SQL> select owner, name, type, refgroup, job, to_char(next_date, 'DD-MON-RRRR') next_date, interval, broken
2 from dba_refresh_children
3 where refgroup = 11;
OWNER NAME TYPE REFGROUP JOB NEXT_DATE INTERVAL B
---------- --------------------- ---------- --------- ---- ----------- -------------------------- -
BORTUST RAW_MATL_BACKORD_MV SNAPSHOT 11 414 01-JAN-4000 sysdate+365 Y
SQL>
The DBA_REFRESH_CHILDREN is a good 'one-stop shop' for information which can report on the health of your refresh jobs. You can find the broken jobs:
SQL> select owner, name, job, refgroup
2 from dba_refresh_children
3 where broken = 'Y'
4 /
OWNER NAME JOB REFGROUP
---------- ----------------------------------- ---- --------
BORTUST RAW_MATL_BACKORD_MV 414 11
SQL>
and it's nice to see there is only one. Of course discovering WHY the job is broken is another task; it's quite likely the source table or view has changed and no longer matches the destination definition, and the insert operation fails with either an ORA-00913 (too many values), an ORA-00947 (not enough values) or an error stating a data type mismatch. A search of the alert log may provide the answer; it may not, and the source code for the materialized view will be necessary to understand which local or remote objects were involved. And, in a large shop with a number of DBAs it may be as simple as asking a question. Make certain you're not 'spinning your wheels', though, as the users may not need the view or the job anymore and any effort to fix it would be effort wasted. Again, a quick question to the right people may save you hours of unnecessary work.The PURGE_OPTION column is probably the most ambiguous of the bunch, and it refers to the method of purging the transaction queue after each 'push' (refresh); 1 indicates a quick purge, and 2 indicates a precise purge. These apply to deferred transactions (which can be used to refresh materialized views). A 'quick' purge is less costly in resources, but may cause deferred transaction records to remain visible for a period of time after the purge. A 'precise' purge consumes more resources but it does offer the benefit of a complete queue flush leaving no lingering traces.
I've said this before, and I'll say it again: having the proper tools for the job at hand can make that job so much easier; when dealing with snapshot/materialized view refreshes the DBA_REFRESH_CHILDREN view can save you time and effort in monitoring jobs and diagnosing problems.
Now, that's refreshing!
