Home » Server Options » Replication » Need help w.r.t Materialized View refresh
Need help w.r.t Materialized View refresh [message #175408] Fri, 02 June 2006 03:09 Go to next message
sanju.varma
Messages: 4
Registered: June 2006
Junior Member
Hi,
Am new to Materialized View and am currently stuck with an issue on MV Refresh.
An outline of the problem is as :- Am having a Master site with 20 tables and am creating a Materialized View of that Master site. This is refreshed normally using Fast Refresh option.
Now the problem is am using a shell script to activate the refresh and a mail will be send to the MV Admin once the refresh is completed with the details like "MV_Table_Name refreshed on DD/MM/YYYY".
But in case the refresh fails due to some reason, i need the mail to be send with details as "MV_Table_Name hasn't been refreshed on DD/MM/YYYY. ORA Error Code='' & ORA Err-msg='' "

It will be very much appreciated if someone could guide me how to get the ORA ERR_NO,ORA_ERR_MSG incase an error occurs while refreshing a particular MV

Thanks in Advance,
Sanjay
Re: Need help w.r.t Materialized View refresh [message #175410 is a reply to message #175408] Fri, 02 June 2006 03:21 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

I have implemented same thing, although on windows.

I am using 3 files for this purpose:

1. .Batch file: for running the refresh job.
2. .sql file: containing the statements for refresh.
3. .vbs file: for sending the result of refresh job.

The test of these 3 files is as below:

File 1.

Quote:

sqlplus -s -l <uid>/<pwd>@<service> @D:\Script\Report_MV_refresh.sql
find /V "Elapsed: 00:00:00.00" D:\script\Refresh.log >D:\Script\temp.log
del D:\script\Refresh.log
ren D:\script\temp.log Refresh.log
Mailscript.vbs



File 2.

Quote:

set timing on
set pagesize 0
spool d:\script\Refresh.log
select 'Connected to <INSTANCENAME> at ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
select '------------------------------------------------------------------------' from dual;
select 'Refreshing MVIEW: <Mview_Name>...' from dual;
exec dbms_mview.refresh('<Mview_name>', '?', '', true,false, 0,0,0, true);
select 'Refreshing MVIEW: <Mview_Name1>...' from dual;
exec dbms_mview.refresh('<Mview_name1>', '?', '', true,false, 0,0,0, true);
select '------------------------------------------------------------------------' from dual;
select 'Completing Report MV REfresh on <Instance_name> at ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
spool off;
exit



File 3.

Quote:

On Error Resume Next
ForReading = 1
ForWriting = 2
'File system object
Set iFSO = CreateObject("Scripting.FileSystemObject")
'Prepare for Email
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "<Sender id>"
objEmail.to = "<Receipients mail id>;<Receipient1 mail id>"
objEmail.Subject = "Daily Report MV Refresh Log"

emailBody = "" + vbcrlf 'TODO: server ip

log_file = "d:\sCRIPT\refresh.log" 'TODO: Daily Refresh Log location


Dim file
set file = iFSO.GetFile(log_file)
Dim TextStream
Set TextStream = file.OpenAsTextStream(1, -2)

Do While Not TextStream.AtEndOfStream
Dim Line
Line = TextStream.readline

' Do something with "Line"
if (line=" ") or (line="") then
line=1
else
emailBody=emailbody & Line & vbCRLF
end if


Loop

objEmail.TextBody = emailBody


'Send Email
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "<SMTP_Server_IP>" 'TODO: mail server name
objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send



I Hope this helps your cause.

You must be using UNIX, & so you can alter these .vbs & .bat files to suit your requirement.

--Girish

[Updated on: Fri, 02 June 2006 03:22]

Report message to a moderator

Re: Need help w.r.t Materialized View refresh [message #175412 is a reply to message #175410] Fri, 02 June 2006 03:30 Go to previous messageGo to next message
sanju.varma
Messages: 4
Registered: June 2006
Junior Member
Thanks Girish, it solved half of my problem, the issue that still needs a solution is, if for some reason, the Refresh fails for a particular MV, i need the Error Code, Err msg to be sent along with the mail.
Any idea, how it could be done. Is there any system table which saves these err code,messages and if yes, how to set it through unix
Re: Need help w.r.t Materialized View refresh [message #175417 is a reply to message #175412] Fri, 02 June 2006 03:49 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Sanju

The refresh log contains all this information.
I am pasting here a sample mail that I get from this process:

Quote:

---------- D:\SCRIPT\REFRESH.LOG
Connected to <Instance_name> at 02-jun-2006 01:04:00
------------------------------------------------------------------------
Refreshing MVIEW: <MView1>...
BEGIN dbms_mview.refresh('<Mview1>', '?', '', true,false, 0,0,0, true); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12805: parallel query server died unexpectedly
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
Elapsed: 01:53:20.03
Refreshing MVIEW: <Mview2>...
PL/SQL procedure successfully completed.
Elapsed: 00:17:46.09
Refreshing MVIEW: <Mview3>...
PL/SQL procedure successfully completed.
.
.
.
------------------------------------------------------------------------
Completing Report MV REfresh on <Instance_name> at 02-jun-2006 03:47:54



--Girish
Re: Need help w.r.t Materialized View refresh [message #175431 is a reply to message #175417] Fri, 02 June 2006 04:37 Go to previous messageGo to next message
sanju.varma
Messages: 4
Registered: June 2006
Junior Member
Too good..thanks girish...its working fine.Help Appreciated..
Re: Need help w.r.t Materialized View refresh [message #176346 is a reply to message #175417] Thu, 08 June 2006 01:00 Go to previous message
sanju.varma
Messages: 4
Registered: June 2006
Junior Member
Hi,
Is there any way through which we could get the Action required for a particular Error Code. i.e; any system table which saves the error code,error message, required action to solve that error .
Previous Topic: Setting up Replication
Next Topic: Query output in HTML format
Goto Forum:
  


Current Time: Thu Mar 28 10:24:44 CDT 2024