Solved

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

Posted on 2014-11-11
9
406 Views
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;
CREATE MATERIALIZED VIEW as
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);

EXECUTE DBMS_MVIEW.REFRESH('MV_VIEW01','C');

-- 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);

commit;
*****************************************************************************************************

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.
0
Comment
Question by:VW 63654
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 300 total points
ID: 40437286
0
 

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.
0
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 300 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.
p-refresh-mv.sql
f-state-mv.sql
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:VW 63654
ID: 40443967
Henka,
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.
0
 

Author Comment

by:VW 63654
ID: 40497482
Hi,
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).

Henka,
Do I need to change anything to both sql scripts? Or just simply run it.
0
 
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.
0
 

Author Comment

by:VW 63654
ID: 40532797
Hi,
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 ?
0
 

Accepted Solution

by:
VW 63654 earned 0 total points
ID: 40543853
Hi,
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.
0
 

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.
0

Featured Post

Independent Software Vendors: 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

630 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