Home » SQL & PL/SQL » SQL & PL/SQL » materialized view refresh part of a bigger transaction
materialized view refresh part of a bigger transaction [message #244555] Wed, 13 June 2007 05:40 Go to next message
Messages: 25
Registered: April 2007
Junior Member

I am trying to find out, if a manual MV refresh can be part of a bigger transaction. Reason is, that I would like to load some fact data into a warehouse table and refresh the corresponding MVs in one transaction. If the refresh fails, I would like to automatically rollback also the fact data inserts. Is that possible?

From the oracle documentation I read about the atomic_refresh of the DBMS_MVIEW.REFRESH procedure. Does it perform a commit at the end of the procedure. So doing something like

00 begin
01 load fact data 1 
02 DBMS_MVIEW.REFRESH mv1 with atomic_refresh
03 load fact data 2 
04 DBMS_MVIEW.REFRESH mv2 with atomic_refresh

would only rollback to the state after line02, right?

However, if I refresh all MVs at the end of the loading, I could use atomic_refresh to make should work:

00 begin
01 load fact data 1
02 load fact data 2
03 DBMS_MVIEW.REFRESH mv1,mv2 with atomic_refresh

This should rollback until the beginning (line 00), correct?


[Updated on: Wed, 13 June 2007 05:41]

Report message to a moderator

Re: materialized view refresh part of a bigger transaction [message #244715 is a reply to message #244555] Wed, 13 June 2007 21:59 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm not sure whether it will work, but that's how I would go about it. I suspect there are three possibilities:
- Atomic refresh performs a commit at beginning and end. If so, you're stuffed.
- Atomic refresh performs a commit at the end. If so, you're 2nd example will work
- Atomic refresh performs no commits. If so, both examples will work.

You could try it and find out - shouldn't be too hard.

If either of the last two options is true, you will probably need to manually rollback. I suspect DBMS_MVIEW will issue a savepoint and only roll itself back to that savepoint without rolling back your load.

Ross Leishman
Previous Topic: Problem with NUMBER datatype
Next Topic: can i put "set serveroutput on" inside the procedure ?
Goto Forum:

Current Time: Thu Mar 30 01:55:06 CDT 2017

Total time taken to generate the page: 0.12308 seconds