I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH
.
I've MV with this PL/SQL :
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;
btw, my MV is too complex to put it here so I made it simple in here.
After I got an update in table master I test it with below query to get status stallness of my MV.
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
And, I create PL/SQL to refresh that MV like this :
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;
But the interval of that dbms_refresh is not working at all. So I created some scheduler to do the job, this is my PL/SQL :
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"some_schema"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
end;',
schedule_name => '"some_schema"."EJS_MV_REFRESH"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Scheduler untuk merefresh MV pada schema some_schema',
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;
So far this solve my problem to automatically refresh MV every 1 minute. But it's not effective at all. So, My Problem is :
How to make this job_action
to execute DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
every 1 minute if the status of stallness is not FRESH
?
I don't know much about how to use pointer or whatever the name to get the result of query to check SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
and use this status to trigger the refresh, it's just like IF-THEN
condition.
Please help me, if you know how to get that…
Best Answer
I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not
FRESH
. Here's my solutions :I created a schedules to be used in my scheduler:
After that I create DBMS_SCHEDULER to create a job like this :
In the job action, I checked stallness status for specified MV like
MV_TEST_BRO
but you can check the status for every MV you had. And I activated some attribut to raise event if the job failed, logging level, and restartable.This job was running for a week now and no problem at all. My MV always
FRESH
every time. May be this post will help another who face the same problem like me. :D