How to automatically refresh the materialized view twice a day

If you have used the orcle materialized view, please help me. I want to refresh it twice a day, for example, at noon or am. I don't know how to write it. it's urgent!

You can achieve this.
Create a table and write a stored procedure
Use a schedule job to implement

Regular intervals are required for common jobs or materialized views
Or write a storage process to manually refresh the materialized view, and then use schedule job to refresh the view at and every day.

 

Simple Oracle10g method:
1. Create a materialized view without specifying the refresh parameter. Just create materialized view, as shown in figure
Create materialized view mv_emp
As select * from Scott. EMP;

2. Create a stored procedure to refresh the materialized view, as shown in figure

Create or replace procedure auto_refresh_mview_job_proc
As
Begin
Dbms_mview.refresh ('mv _ emp ');
End;
/

3. Refresh the materialized view regularly at and every day using Oracle's 10 Gb scheduler.

Begin
Dbms_scheduler.create_job (
Job_name => 'auot _ refresh_mview_job ',
Job_type => 'stored _ procedure ',
Job_action => 'Scott. auto_refresh_mview_job_proc ',
Start_date => sysdate,
Repeat_interval => 'freq = daily; byhour = 12,19 ',
Enabled => true,
Comments => 'refreshmaterialized view mv_emp'
);
End;
/

4. Method for deleting a scheduler object

Begin
Dbms_scheduler.drop_job (
Job_name => 'auot _ refresh_mview_job'
);
End;
/

 

Dbms_mview.refresh ('mv _ emp'); this is what I want. Thank you. Also, if I used alter materialized view' before, |. table_name | 'refresh complete start with sysdate next sysdate + 100/(24*60*60) '|'; 'indicates how to cancel the refresh.

 

Delete the job that refreshes the materialized view. The following statements query the Materialized View and the corresponding jobno:
Select job, what from user_jobs;

After jobno is found, you can use dbms_job.remove to delete the job.

Exec dbms_job.remove (: jobno );

 

3. Refresh the materialized view regularly at and every day using Oracle's 10 Gb scheduler.

Begin
Dbms_scheduler.create_job (
Job_name => 'auot _ refresh_mview_job ',
Job_type => 'stored _ procedure ',
Job_action => 'Scott. auto_refresh_mview_job_proc ',
Start_date => sysdate,
Repeat_interval => 'freq = daily; byhour = 12,19 ',
Enabled => true,
Comments => 'refreshmaterialized view mv_emp'
);

I don't quite understand the above method.

 

The scheduled refresh of materialized views is actually implemented by Oracle tasks, that is, dbms_mview.refresh ('mv _ emp') is regularly executed according to the interval of refreshing. the more powerful sched function is added to Oracle10g, and the task execution time can be defined more flexibly. Therefore, the method I provided here is to use the 10g scheduler, the internal Oracle package DBMS_Scheduler is used to use scheduler. dbms_scheduler.create_job is used to create a real-time task. The following is an example.

Begin
Dbms_scheduler.create_job (
Job_name => 'Auto _ refresh_mview_job ',
Job_type => 'stored _ procedure ',
Job_action => 'Scott. auto_refresh_mview_job_proc ',
Start_date => sysdate,
Repeat_interval => 'freq = daily; byhour = 12,19 ',
Enabled => true,
Comments => 'refreshmaterialized view mv_emp'
);

Job_name corresponds to the task name. The task name is 'Auto _ refresh_mview_job'
Job_type is the task type. Here it is the stored procedure type.
Job_action is the task name. Here is the Stored Procedure 'Scott. auto_refresh_mview_job_proc'. The Stored Procedure function is to execute dbms_mvi1_refresh ('mv _ emp ')
Start_date indicates the start time of execution. The execution starts when the task is created.
Repeat_interval indicates the execution interval, freq indicates the execution frequency, and daily indicates the execution every day. byhour defines the specific execution time as 12 hours and 19 hours per day.
Enabled indicates that the task is enabled. If this parameter is not added, it is disabled by default.
Comments is a description of the task. The content here can be filled in according to the actual situation.

After the preceding task is defined, You can query the task from the dictionary table dba_scheduler_jobs.