Solved

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

Posted on 2014-11-11
9
385 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now