How to manage disk space allocation for materialized views

disk-spacematerialized-vieworacleoracle-11g-r2oracle-12c

Summary: I have materialized views in oracle 11g that seem to hog disk space, unlike normal tables that mark rows as deleted and stats eventually show them as free space (allocated to the table, allowing reuse). Tablespace usage only grows for materialized views unlike stats for origin tables.
Tested in Oracle 12c with same results. How to ensure MV reuse space from deleted rows?

What have I done?
I have these partitioned materialized views set up in a separate schema, separate tablespace from the origin tables (i know they could have partitions created dynamically, call it technical debt).

CREATE MATERIALIZED VIEW replication_schema.origin_table
PARTITION BY RANGE(tbl_timestamp) 
(
    PARTITION tbl_before_2016 VALUES LESS THAN (TO_TIMESTAMP('2016-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
    PARTITION tbl_2016_01 VALUES LESS THAN (TO_TIMESTAMP('2016-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
    PARTITION tbl_2016_02 VALUES LESS THAN (TO_TIMESTAMP('2016-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
...
 PARTITION tbl_after_2025 VALUES LESS THAN (MAXVALUE)
)
REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT sysdate+1/1440
AS SELECT * FROM origin_schema.table;

And they have some indexes on them as well, some global and some are local.

CREATE INDEX tbl_account_index ON replication_schema.origin_table (tbl_account DESC) LOCAL;
CREATE INDEX tbl_column1_index ON replication_schema.origin_table (tbl_column1 DESC) LOCAL;
CREATE INDEX tbl_column2_index ON replication_schema.origin_table (tbl_column2 DESC) LOCAL;
CREATE INDEX tbl_column3_index ON replication_schema.origin_table (tbl_column3 DESC);
CREATE INDEX tbl_column4_index ON replication_schema.origin_table (tbl_column4 DESC);

Most of the time they get new rows (about 4M/mo) but users have set up a process to delete old rows from the origin table every two weeks. They can delete up to 500K/1M rows from each replicated table, every time.

There are seven materialized views in this schema. Each one extract data from one origin table.

What we see is that, contrary to what happens with the origin table, the space reported as free in dba_ tables does not change over time and tablespace usage only grows from these materialized views.

If I wait a while after deleting rows and run this query:

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.totalspace <>0 ;

It shows an increase in the Free MB column (space in dba_data_files minus allocation declared in dba_segment) for origin tablespace but the used MB for replication never decrease, only increase on new rows (over three years now)

Tablespace      Used MB    Free MB  Total MB   Pct. Free
SYSTEM          491        9        500        2
SYSAUX          1628       162      1790       9
UNDOTBS1        0          9645     9645       100
ORIGIN_DATA     2705       1391     4096       34
ORIGIN_REP_DATA **1975**   2121     4096       52

That tablespace only holds these materialized views. There's no other object there being used.

I tried the advisor to see what can I do:

variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='REPCHECK';
  descr:='Replication advisory';

  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor',
    task_id          => :id,
    task_name        => name,
    task_desc        => descr);

  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLE',
    attr1            => 'REPLICATION_SCHEMA',
    attr2            => 'ORIGIN_TABLE',
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);

  dbms_advisor.set_task_parameter(
    task_name        => name,
    parameter        => 'recommend_all',
    value            => 'TRUE');

  dbms_advisor.execute_task(name);
  end;
end; 
/

And it says

Perform re-org on the origin_table object, estimated savings is xxx bytes

If I try querying recommendations through procedure:

select
   tablespace_name,
   allocated_space,
   used_space reclaimable_space
from
   table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))

It returns

ORIGIN_REP_DATA 100663296   38419844

But I only get errors when trying to run SHRINK SPACE or COMPRESS options

ORA-10635: Invalid segment or tablespace type
10635. 00000 – "Invalid segment or tablespace type"
*Cause: Cannot shrink the segment because it is not in auto segment space
managed tablespace or it is not a data, index or lob segment.
*Action: Check the tablespace and segment type and reissue the statement

Long story short: What can I do to avoiding disk space wasting in this materialized views? How to perform maintenance on them? Shall I drop them and recreate them? Datafiles usage in tablespace is growing about 10GB per month and I'm running out of time (and space). Thanks.

Best Answer

Well, I tried many options, most of them did not allow me to get anywhere near modifying anything in the partitioning for the MV.

What ended up releasing the space (from partitions no longer available in the origin table and data no longer there either) was running a complete refresh of MV along with a TRUNCATE on the MLOG tables (making sure there's no refresh process running from the scheduled jobs), executing these with a user with enough privs for each query.

EXECUTE DBMS_MVIEW.REFRESH('REPLICATION_SCHEMA.ORIGIN_TABLE', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE, FALSE);
truncate table origin_schema.mlog$_origin_table;