Oracle Tips and Tricks — David Fitzjarrell

September 29, 2008

It’s Real Refreshment

Filed under: General — dfitzjarrell @ 17:40

For those who suffer from endless curiosity the prospect of poking around the data dictionary is considered fun, and in the quest of such fun one can run across an interesting view or two. DBA_REFRESH_CHILDREN could be one of those views, and for the inquisitive an examination is in order.

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)

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
BORTUST    RAW_MATL_BACKORD_MV         SNAPSHOT         11  414  01-JAN-00 sysdate+365

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

The DBA_REFRESH_CHILDREN view 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

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!


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: