Avatar of VW 63654
VW 63654
 asked on

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;
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.
Oracle Database

Avatar of undefined
Last Comment
VW 63654

8/22/2022 - Mon
SOLUTION
Helena Marková

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
VW 63654

ASKER
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.
SOLUTION
Helena Marková

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
VW 63654

ASKER
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.
VW 63654

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Helena Marková

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 63654

ASKER
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 ?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
VW 63654

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.