Summary: I have materialized views in Oracle 11g that seem to take up disk space, unlike the normal tables that mark the rows as deleted and the statistics finally show them as free space (allocated to the table, allowing reuse). The use of table space only grows for materialized views, unlike the statistics of the source tables.
Tested in Oracle 12c with the same results. How to ensure MV reuse space of deleted rows?
What have I done?
I have these materialized partitioned views configured in a separate schema, a separate table space from the source tables (I know they could have dynamically created partitions, 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 also have some indexes, some global and some 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 (approximately 4 M / month), but users have set up a process to remove old rows from the source table every two weeks. They can delete up to 500K / 1M rows from each replicated table, each time.
There are seven views materialized in this scheme. Each extracts data from a source table.
What we see is that, contrary to what happens with the source table, the space reported as free in
dba_ Tables do not change over time and the use of table space only grows from these materialized views.
If I wait a moment after deleting rows and running 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))
round(sum(bytes) / 1048576) TotalSpace
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.totalspace <>0 ;
It shows an increase in
Free MB column (space in dba_data_files less allocation declared in dba_segment) for the source table space, but the MB used for replication never decreases, only increases in new rows (more than 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 table space only contains these materialized views. There is no other object that is being used.
I tried the advisor to see what I can do:
variable id number;
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
task_name => name,
object_type => 'TABLE',
attr1 => 'REPLICATION_SCHEMA',
attr2 => 'ORIGIN_TABLE',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
Perform a reorganization on the origin_table object, the estimated saving is xxx bytes
If I try to consult recommendations through the procedure:
table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))
ORIGIN_REP_DATA 100663296 38419844
But I only get errors when I try to execute the SHRINK SPACE or COMPRESS options
ORA-10635: Invalid segment or type of table space
10635.00000 – "Invalid segment or table space type"
* Cause: the segment cannot be reduced because it is not in the automatic segment space
Managed table space or not is a segment of data, index or lob.
* Action: check the table space and segment type and reissue the declaration
Long story short: What can I do to avoid wasting disk space on these materialized views? How to perform maintenance on them? Should I drop them and recreate them? The use of data files in the table space is growing around 10 GB per month and I am running out of time (and space). Thank you.