Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to create materialized view and capture the detail of refreshing activities ?

Posted on 2014-11-11
Medium Priority
Last Modified: 2015-01-16
Hi experts,

I am not an expert on this but I have a very basic knowledge on creating simple MV using Oracle 11g.

I have access to some view tables (remotedb). I don't know how the view was created, whether it is a simple
view or complicated queries.

So we have created dblink to their remotedb and creating synonym table for each view that been granted to access.
On top of this, I also need to monitor all refresh activities to ensure nothing goes wrong during refresh process.
Some refresh may take some time and if there is any error, I have to stop and start refreshing it again.
This to be done everyday.

Here is my script to create MV:
drop materialized view MV_VIEW01;
select * from synonym_table_view01;

Refresh materialized view:
-- refresh mat views table
-- and keep record all activities (starttime, endtime, how_long in seconds and total records) stored in MAT_VIEWS_REFRESH_LOGS table.

-- insert start time
insert into MAT_VIEWS_REFRESH_LOGS (mvname,start_date) values ('MV_VIEW01',sysdate);


-- once done
update MAT_VIEWS_REFRESH_LOGS set end_date = sysdate where mvname = 'MV_VIEW01' and end_date is null;

-- counting total records after refresh
update MAT_VIEWS_REFRESH_LOGS set total_rows = (select count(*) from MV_VIEW01);

update MAT_VIEWS_REFRESH_LOGS set total_sec = (end_date - start_date) * 24 * 60 * 60
where id = (select max(id) from MAT_VIEWS_REFRESH_LOGS);


And I need to do it all for 8 materialized views.
So my questions are :

How can I improve the script so that all refresh process can run automatically for every MV everyday at 6pm except for Friday and Sunday?
I don't want to set the time during the creation process because I want to create once but may change the timing later.

How can I capture any error during the refresh process ?
Is there any build-in function that generate such logs ?
Is there any way to make the refresh process improved (more faster) ?

I need your guidance step by step how to do this.

Thank you.
Question by:VW 63654
  • 6
  • 3
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 900 total points
ID: 40437286

Author Comment

by:VW 63654
ID: 40439286
The documentation show in general how MV can be refreshed in many ways.
I like "DBMS_MVIEW.REFRESH_ALL_MVIEWS" which probably one of built-in function that required for my code. But I don't know how to use this function to capture any failure , refresh activities etc.

I need a script to run all refresh process automatically for every MV everyday at 6pm except for Friday and Sunday.
During the refresh process, any error should be captured as MV_logs. Total records for each MV also need to be captured so that we know how many new rows are created from one refresh to another.
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 900 total points
ID: 40439404
I have no script. In my past work we used job for loading (ETL prorocess).
Instead of DBMS_JOB (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_job.htm#BABHCBFD)
DBMS_SCHEDULER (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#CIHHBGGI) ought to be used.
The status of MV can be read in sys.all_mviews (
see this description: https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1153.htm).

In brief here is part of refreshing MVs:
there was package K_MV with procedure p_resh_mv and function f_state_mv among others one.
Procedure p_resh_mv refreshes MVs, input parameter v_mat_views contains string of MVs.
Function f_state_mv checks MV status.
Each error is written to T_ERROR table. There is also T_MONITOR table where start and stop of each process and also errors are written.

This can be an inspiration to you.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

by:VW 63654
ID: 40443967
Thanks a lot. That probably close to what I am looking for. I will study those links and both sql scripts. I will update the status later.

Author Comment

by:VW 63654
ID: 40497482
I am really sorry for not responding to this. I just back from my leave. I did asked my staff to look through all available option prior to my leave.

I have not used both henka's sql script yet.
My staff had done the following instead :
1. Create materialized view one by one with daily auto refresh
2. They changed the time to do auto refresh using Oracle Enterprise Manager(midnight)
3. They checked all MVs early in the morning
4. Create script to count total rows for all MVs. Run this script everyday using dos batch file to run sql script

So far, we managed to create, auto refresh and view total records generated daily using log tables. However we still can't figure it out how to capture error during the refresh process (if any).

Do I need to change anything to both sql scripts? Or just simply run it.
LVL 22

Expert Comment

by:Helena Marková
ID: 40499840
My scripts test result of MV's refresh. There ought to be some modification, because those sql files are not scripts. It is procedure (p_refresh_mv) and function (f_state_mv) used in procedure p_refresh_mv.
What does it mean "However we still can't figure it out how to capture error during the refresh process (if any)" ?
If it is about errors during loading process, then these errors ought to be captured in loading procedures.
But the errors during refresh process, as I know, cannot be captured. Maybe there are some LOGs ... but I am not sure about it.

Author Comment

by:VW 63654
ID: 40532797
Referring my post on 13 December 2014, most already been solved except for one thing, that is, how to capture any error that may occurred during refresh process.

Is there anyone know how to do this ?

Accepted Solution

VW 63654 earned 0 total points
ID: 40543853
90% problem solved. But the solution is more on trying to automate each process. I can't think of any other way.
So I will close this question.

Here is the summary of what I have done.

1. Create materialized view one by one with daily auto refresh
2. Changed the time to do auto refresh using Oracle Enterprise Manager ( changed to early morning refresh)
3. Create sql script to count total rows for each MV together with the detail of last_refresh date, stored
in log table for monitoring.
4. Run this script everyday using dos batch file to run sql script ( after all refreshed has been done)
5.To capture any error that might happen during auto refresh process, still don't know. But I used the log
table by comparing the last_refresh_date with the previous last_refresh_date. If the last_referesh_date is same
as previous, then there is something wrong with the refresh activity which will required further action. This might required us to refresh manually.

To all, thank you for your contribution.

Author Closing Comment

by:VW 63654
ID: 40553095
The solution to this problem is merely my own research. Henka provide me good thought and idea by giving me reference links.
The solution is not perfect but serve the purpose of what I am trying to look for.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question