Oracle. How to refresh materialized view when don`t have enough space on divece

Question

I do not have enough practice in a materialized views, and I have a problem right now. I have a oracle database with limited disk space. There is a data warehouse in it. I created a materialized view to get reports from it. The materialized view becomes larger, and every night I began to compress the materialized view. Suddenly, when the materialized view refreshed, showed an error.

begin
DBMS_MVIEW.REFRESH('part_sales_mv','?'); 
end;
Error report -
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P004
ORA-01114: IO error writing block to file  (block # )
ORA-01114: IO error writing block to file 201 (block # 8907)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 122880
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 4
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

Mview partitioned and subpartitioned by range. This is the parametres of my materialized view:

refresh force on demand
buid  deferred

parallel
enable row movement
using default local rollback segment
enable query rewrite

Please help me with your solutions, to resolve my problem. I need to find a way how to refresh mvew with limited disc space.

Answer 1

You can try to use compression when creating your materialized view but keep in mind this could have negative effects on the performance.

CREATE MATERIALIZED VIEW MV_TEST
COMPRESS
REFRESH FAST ON COMMIT
AS SELECT * FROM TEST;

I would recommend asking for more space or limiting the amount of data in this materialized view, maybe you can avoid having all the data stored in your source table.