|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326088 is a reply to message #326083] |
Tue, 10 June 2008 02:46   |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That has to be a record - no reply in 25 minutes and you're already asking why no-ones' replying.
The ony way to do thi is to write a procedure to refresh the snapshots manually and add some code to this procedure to display the message that you want.
Then call this procedure from DBMS_JOB/DBMS_SCHEDULER to refresh the snapshot on a regular cycle.
|
|
|
|
|
|
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326097 is a reply to message #326089] |
Tue, 10 June 2008 03:22 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Where is this message going to be displayed?
You can't use DBMS_OUTPUT to display it, as the session that does the refreshing is an internal oracle process.
You could create a statement level trigger on the snapshot that fires for Inserts, Updates and Deletes, but I wouldn't guarantee that the trigger would fire if there were no changes to the table.
It would fire if you were doing a full refresh, as that truncates and re-inserts, but I doubt it will fire for a Fast refresh.
If you're going to refresh the trigger on a time basis, you'd be better off following the advice from my previous post, and doing the refresh manually - that way you can add whatever 'message' code you need.
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326098 is a reply to message #326093] |
Tue, 10 June 2008 03:28 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Thanks to you all for your response.
@ThomasG
| Quote: |
I suspect you have the requirement backwards. I think someone at some point needs to know when the view was last refreshed.
|
Yes, you are correct. The moment the materialised view (I am using FAST REFRESH CLAUSE and Time Interval here, which have alreday created.) got refreshed I want to fire a trigger (or something like) print a message "The data has been refreshed" through web page.
@Michel
| Quote: |
Do as JRowbottom said, create your job to refresh the MV.
|
I am using the following code
CREATE MATERIALIZED VIEW mv_snapshot_A
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 20/1440
WITH PRIMARY KEY
AS SELECT * FROM A;
Thanks Michel for the reply.But here do I need to create DBMS_JOB for this? I dont think so as I ahve already mentioned the interval and using FAST REFRESH Clause.Please do let me know if I am wrong here.Now Materialized view has been created.My problem is the moment it got refreshed I want to print a message in my web page like "The data has been refreshed!".
|
|
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326102 is a reply to message #326098] |
Tue, 10 June 2008 03:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Unless you've got some quite fancy middleware, there is no way at all that a process running on the server can display a message on some web-page running on a browser somewhere.
Would it not make more sense to simply have the web page display the LAST_REFRESH from USER/ALL_SNAPSHOTS. When the web page refreshes, it will display the most recent refresh time.
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326105 is a reply to message #326097] |
Tue, 10 June 2008 03:54 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| JRowbottom wrote on Tue, 10 June 2008 03:22 | Where is this message going to be displayed?
You can't use DBMS_OUTPUT to display it, as the session that does the refreshing is an internal oracle process.
You could create a statement level trigger on the snapshot that fires for Inserts, Updates and Deletes, but I wouldn't guarantee that the trigger would fire if there were no changes to the table.
It would fire if you were doing a full refresh, as that truncates and re-inserts, but I doubt it will fire for a Fast refresh.
If you're going to refresh the trigger on a time basis, you'd be better off following the advice from my previous post, and doing the refresh manually - that way you can add whatever 'message' code you need.
|
@JRowbottom
Thanks for your resposnse! A question of concern. Whats the difference between FAST REFRESH and COMPLETE REFRESH? DOES IN COMPLETE REFRESH TRUNCATES and REINSERTS? Then ISNT THERE A POSSIBILITY OF GETTING WRONG RECORDS AT A SINGLE POINT OF TIME?
Need to know...
Ok, no problem I think I can use statement level trigger ? But why not rowlevel trigger??
How can I do that? Can you please demonstrate with an example?
I wont mind if trigger wont fired if there is no changes to master.
Thanks in adavance!
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326106 is a reply to message #326101] |
Tue, 10 June 2008 03:56 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| ThomasG wrote on Tue, 10 June 2008 03:47 | So why don't you just put a "The view was last refreshed on Tue, 10 June 2008 10:28"-like message on the web page using the data from USER_MVIEW_REFRESH_TIMES?
That way the user knows when it was last refreshed and can even estimate when it will be refreshed next when the interval is constant.
|
Can you please show me with an example code? I will be thankful to you.
|
|
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326127 is a reply to message #326108] |
Tue, 10 June 2008 04:55 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| ThomasG wrote on Tue, 10 June 2008 04:05 | | Quote: | Can you please show me with an example code?
|
Not really, since I we don't know what you use to generate your web pages.
Select the LAST_REFRESH from USER_MVIEW_REFRESH_TIMES or USER_SNAPSHOTS, format it with to_char() the way you like it, and put it on the web page.
|
@ThomasG
Thanks for your help!
Thanks to ThomasG,JRowbottom and Michel for giving your valuable time.
I Still need to know..
Whats the difference between FAST REFRESH and COMPLETE REFRESH? DOES IN COMPLETE REFRESH TRUNCATES and REINSERTS? Then "ISNT THERE A POSSIBILITY OF GETTING WRONG RECORDS AT A SINGLE POINT OF TIME"? Thanks in advance.
Regards,
Oli
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326129 is a reply to message #326127] |
Tue, 10 June 2008 05:02 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
A refresh COMPLETE will truncate and rebuild the snapshot. The final version of the snapshot will contain all of the data in the master table at the point of rebuild - what exactly would the 'wrong records' be?
A FAST refresh would be more accurately called an INCREMENTAL refresh - it will use inserts and deletes (and possibly updates) to load the changes to the master table into the snapshot.
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326134 is a reply to message #326129] |
Tue, 10 June 2008 05:14 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| JRowbottom wrote on Tue, 10 June 2008 05:02 | A refresh COMPLETE will truncate and rebuild the snapshot. The final version of the snapshot will contain all of the data in the master table at the point of rebuild - what exactly would the 'wrong records' be?
A FAST refresh would be more accurately called an INCREMENTAL refresh - it will use inserts and deletes (and possibly updates) to load the changes to the master table into the snapshot.
|
If I fetch the records from snapshot in my web page, in case of complete refresh wont I get wrong record count at a single point of time?? Did you get what I mean?
Thanks,
Oli
|
|
|
|
|
|
| Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326143 is a reply to message #326139] |
Tue, 10 June 2008 05:31 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| JRowbottom wrote on Tue, 10 June 2008 05:22 | I don't understand what you mean.
You will get the records that are currently in the snapshot. These may or may not be the same as the records currently in the master table.
|
| Quote: |
A refresh COMPLETE will truncate and rebuild the snapshot
|
My web page is using the snapshot. You said, "A refresh COMPLETE will truncate and rebuild the snapshot".Now my question is if I am using COMPLETE REFRESH instaed of FAST REFRESH wont the
snapshot have zero records at a certain point of time as its got truncated and rebuilded!
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
thanks!!!