I am trying to refresh MView manually(On Demand). For this
1. I created a MV.
2. Then A SP for Refresh using DBMS_MVIEW.REFRESH()
3. And then Anonymous Block for CREATE_JOB
But job is not executing. Its status is showing failed.
When I tried to run
DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',TRUE); --Not executing
But DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',FALSE); --Executing successfully.
So, please suggest, what should I do for refresh.
CREATE MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, SUM(sal) FROM emp
GROUP BY deptno;
CREATE OR REPLACE PROCEDURE refresh_mv_emp
AS
BEGIN
DBMS_MVIEW.REFRESH('MV_EMP');
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'MY_MVIEW_REFRESH',
job_type => 'PLSQL_BLOCK',
job_action => 'REFRESH_MV_EMP',
number_of_arguments => 0,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'This job refresh MV_EMP every day'
);
END;