Thanks for the question, Graziano.

Asked: July 23, 2010 - 7:27 am UTC

Last updated: June 29, 2017 - 2:06 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I,m really new in using Oracle and I apologize in advance for my trivial question.
I just defined a materialized view that works fine on a basic table of about 800M rows. The refresh process (that at the moment I do manually) take about 2 hours. I'd like to have it done daily, for insatnce, during night time; so I'm thinking to have some "scheduled" job to do it on a daily frequence, but I have no idea on how to do. In othe words I have my refresh command (i.e.:
BEGIN
DBMS_SNAPSHOT.REFRESH(
LIST => 'my_mv_name'
,PUSH_DEFERRED_RPC => TRUE
,REFRESH_AFTER_ERRORS => FALSE
,PURGE_OPTION => 1
,PARALLELISM => 0
,ATOMIC_REFRESH => TRUE
,NESTED => FALSE);
END;
/
) that I'd like to have scheduled every day, for instance at 5:00.
Could you help me, suggesting how to do or, at least, telling me where to search to have examples on it? Thanks a lot in advance. Graziano

and Tom said...

you can just use ALTER to do this - it'll create a job for you:

ops$tkyte%ORA10GR2> create materialized view mv
  2  refresh complete
  3  as
  4  select * from t;

Materialized view created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec print_table('select * from user_jobs');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter materialized view mv
  2  refresh
  3  start with trunc(sysdate)+17/24
  4  next trunc(sysdate)+1+17/24;

Materialized view altered.

ops$tkyte%ORA10GR2> exec print_table('select * from user_jobs');
.JOB                          : 484
.LOG_USER                     : OPS$TKYTE
.PRIV_USER                    : OPS$TKYTE
.SCHEMA_USER                  : OPS$TKYTE
.LAST_DATE                    :
.LAST_SEC                     :
.THIS_DATE                    :
.THIS_SEC                     :
.NEXT_DATE                    : 23-jul-2010 17:00:00
.NEXT_SEC                     : 17:00:00
.TOTAL_TIME                   : 0
.BROKEN                       : N
.INTERVAL                     : trunc(sysdate)+1+17/24
.FAILURES                     :
.WHAT                         : dbms_refresh.refresh('"OPS$TKYTE"."MV"');
.NLS_ENV                      : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'
NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
.MISC_ENV                     : 0102000200000000
.INSTANCE                     : 0
-----------------

PL/SQL procedure successfully completed.

or, if you like, you can use dbms_job/dbms_scheduler to create a custom job of your liking - either way.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Graziano, July 26, 2010 - 2:50 am UTC

Shin Zeng, June 28, 2017 - 7:44 pm UTC

I did 'alter materialized view mv_nm refresh start with sysdate+0.1/24 next sysdate+1/24', and checked dba_jobs - the job was scheduled to run in 0.1 hour, but after an hour, I checked the NEXT column in dba_jobs, it's still the original one, I checked dba_mview, the last_refresh was still a few days ago - it did not refresh. job_queue_processes parameter in the database is set to 16. At the moment, only one job was running. I manually refreshed it with dbms_refresh.refresh('mv_nm'), and it worked, but the NEXT column in dba_jobs did not change at all

June 29, 2017 - 2:06 am UTC

Perhaps submit a standalone job, ie

variable j number
exec dbms_job.submit(:j, 'dbms_refresh.refresh(''mv_nm')';', sysdate, sysdate+1/24);

or similar.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

daily refresh for a materialized view

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs