Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-11-11
9
Medium Priority
?
421 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 900 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 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.
p-refresh-mv.sql
f-state-mv.sql
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

972 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