How to create Materialized View and Automatic Refresh Scheduler in Oracle 11gR2
Sumber Gambar : http://annasophiawatts.com/wp-content/uploads/2014/12/Oracle-11g-RAC-Essentials-Certification-An-Overview.png
Before I post this article, I was troubled by MV configuration to refresh it manually. My MV is too complicated to tell it here, so I'll create it more simple.Create a materialized view first, here is my simple MV :
You can create it using REFRESH COMPLETE ON COMMIT or using FAST/FORCE mode. For detailed information please read this. After you update one of your table master you will get status NEED_COMPILE if you run this query :
After that I created a Schedules using DBMS_SCHEDULER like below this :
MV and The Schedules has been created and now we create a DBMS_REFRESH to refresh every MV we have. This is My PL/SQL :
For Automatic Refresh, we need Job Scheduler to do that so we create it one, using this PL/SQL below :
This Job running every minutes to check your Staleness status for your MV. If the status is not FRESH, this job will execute DBMS_REFRESH. The status is NEED_COMPILE, STALE, UNUSABLE or FRESH for staleness status.
I've run this job scheduler over a week now, and I don't have a problem about that. My MV status is FRESH every time.
CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "some_tablespace"
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM some_table;
You can create it using REFRESH COMPLETE ON COMMIT or using FAST/FORCE mode. For detailed information please read this. After you update one of your table master you will get status NEED_COMPILE if you run this query :
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
After that I created a Schedules using DBMS_SCHEDULER like below this :
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY',
start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS TZR'),
comments => 'Interval refresh 1 menit',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"');
END;
MV and The Schedules has been created and now we create a DBMS_REFRESH to refresh every MV we have. This is My PL/SQL :
BEGIN
DBMS_REFRESH.make(
name => 'some_schema.MINUTE_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ SYSDATE + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => 4,
heap_size => NULL);
END;
BEGIN
DBMS_REFRESH.add(
name => 'some_schema.MINUTE_REFRESH',
list => 'some_schema.MV_TEST_BRO',
lax => TRUE);
END;
For Automatic Refresh, we need Job Scheduler to do that so we create it one, using this PL/SQL below :
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
mv_name USER_MVIEWS.MVIEW_NAME%type;
mv_status USER_MVIEWS.STALENESS%type;
mv_last USER_MVIEWS.LAST_REFRESH_TYPE%type;
mv_state USER_MVIEWS.COMPILE_STATE%type;
CURSOR check_mv
IS SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME = ''MV_TEST_BRO'' ORDER BY MVIEW_NAME;
BEGIN
OPEN check_mv;
LOOP
FETCH check_mv INTO mv_name, mv_status, mv_last, mv_state;
IF mv_status != ''FRESH'' THEN
DBMS_REFRESH.REFRESH(name => ''SOME_SCHEMA.MINUTE_REFRESH'');
ELSE
EXIT;
END IF;
EXIT WHEN check_mv%NOTFOUND;
END LOOP;
CLOSE check_mv;
END;',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Refresh MV every 1 Minutes',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"SOME_SCHEMA"."REFRESH_MV"' );
END;
This Job running every minutes to check your Staleness status for your MV. If the status is not FRESH, this job will execute DBMS_REFRESH. The status is NEED_COMPILE, STALE, UNUSABLE or FRESH for staleness status.
I've run this job scheduler over a week now, and I don't have a problem about that. My MV status is FRESH every time.