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

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.
VW 63654Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
VW 63654Author Commented:
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.
Helena Markováprogrammer-analystCommented:
I have no script. In my past work we used job for loading (ETL prorocess).
Instead of DBMS_JOB (
DBMS_SCHEDULER ( ought to be used.
The status of MV can be read in sys.all_mviews (
see this description:

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

VW 63654Author Commented:
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.
VW 63654Author Commented:
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.
Helena Markováprogrammer-analystCommented:
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.
VW 63654Author Commented:
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 ?
VW 63654Author Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VW 63654Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.