| Need help w.r.t Materialized View refresh [message #175408] |
Fri, 02 June 2006 03:09  |
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  |
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 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
