Database Oracle

Oracle Automatic Refresh Materialized View

Leave a comment

In Oracle Database, we can very easily create materialized views  (MVs) and let Oracle handle the refresh automatically.

For example, using the following statement we can create a MV  in the schema EDWCM and tell Oracle not build it now until 5:00 PM today, and then complete refresh it at 1:00 PM  every Sunday.:


CREATE MATERIALIZED VIEW EDWCM.MV_CLV_RESULT_WKLY
TABLESPACE EDWCM_DATA PARALLEL 8 
-- BUILD IMMEDIATE
BUILD DEFERRED
REFRESH COMPLETE 
START WITH ( TRUNC (SYSDATE) + 17/24 ) 
NEXT ( NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') + 13/24 )
AS
[Your-MV-Select-Query go here];

After creating the automatic refresh MV, you can change the refresh time and frequency by altering the MV. For example, if you refresh it at 1:00 AM everyday, you can issue the following MV alter statement:

ALTER MATERIALIZED VIEW EDWCM.MV_CLV_RESULT_WKLY
 REFRESH COMPLETE
 NEXT (TRUNC (SYSDATE+1) + 1/24 );

Oracle is still using the deprecated Oracle Job (at least up the version 11gR2) to maintain the refresh, not use the advanced Oracle SCHEDULER. So when you create a Oracle automatic refresh MV using the syntax above, Oracle will create a Oracle job for you and schedule the job to run. You can use the following query to find the Oracle job information (write down the job_id and you will need job_id to stop or resume the automatic refresh, see below):

select * from  dba_jobs where SCHEMA_USER = 'EDWCM';

You also can use the following query to find all the refresh history in past:

SELECT * FROM dba_snapshot_refresh_times where owner = 'EDWCM';

Finally, you can stop the automatic refresh or resume the automatic refresh for the MV by (your need to supply the job_id integer value for the MV refresh job   :

-- Stop the automatic refresh 
exec DBMS_JOB.BROKEN (job_id,  TRUE );

-- Resume the automatic refresh 
exec DBMS_JOB.BROKEN (job_id,  FALSE );

Notes: In order to create the automatic refresh MV, the owner of the MV should be granted the SELECT privilege directly on all the master tables (tables used in the query). The indirect grant (e.g. the grant through roles) will not not work.

Leave a comment