Urgent!!! Materialize view issue

[sarannn_99]

Hi frens,
Complete Refresh of a Materialize view takes lots of space in temporary tablespace. Need to avoid this circumstances ,Any suggestions???

Thanks in advance.

Sarav.

[slimdave]

What kind of query do you have defining the view? I would expect that it's an aggregation, in which case you might set a large sort_area_size for the session before refreshing.

[vinit]

Create a snapshot log, do a fast refresh !

[sarannn_99]

Actually data consists in the base table is 100 millions and its single table aggregate MView.

Any suggestions, how to reduce the MView eats up lots of space in temporary tablespace.

Thanks in advance.
Sarav.

[slimdave]

How much space is it using up? To do a complete refresh is going to demand a certain amount of temp space, and there's not a lot you can do about it. You might consider not doing complete refresh, as vinit suggests. Perhaps you could describe how data is loaded to the master table and what the exact MV query is, and we could think about whether partitioning, and implementing partition change tracking would be beneficial.