0

I have a straight forward materialized view across about 8 master tables.

create materialized view MV
REFRESH FAST ON COMMIT as
  SELECT...

I am also running this query to keep an eye on the materialized log sizes.

select segment_name, SUM ( (BYTES) / (1024 * 1024)) "Allocated(MB)" from dba_segments where (segment_name, owner) IN (
  select log_table, log_owner from dba_mview_logs where log_owner = 'XXX')
and segment_type = 'TABLE' GROUP BY segment_name;

Since I am refreshing on commit, I wouldn't expect these logs to have an opportunity to grow. As soon as I write to the tables I expect the view to refresh and the log to clear.

However, most of my logs are 0.0625mb with one at 27mb and another at 2mb. How can I investigate the cause of the larger size? Both of the tables with larger size logs containing blob columns holding possibly large data. But I don't understand at a basic level why these logs are > 0.

Moz
  • 1,494
  • 6
  • 21
  • 32

1 Answers1

1

The table data segment does not shrink when data is deleted from it, and the segment size also depends on the size increments in which it grows (based on the tablespace settings).

What you see there is just the maximum size that the segmenbt has grown to, not the amount of data that it is holding -- it could well be empty when you query it. Just count the number of rows in the MV log table.

You can look at all of the MViews dependent on the table to see what their last refresh time was, and compare to the set of SNAPTIME$$'s in the mview log. If the MViews are all definitely refreshed after the snaptime$$, have a look at mview.purge_log to remove the rows.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • Thanks I've had a look at this my MV appears to be up to date with data, however one of my master tables has 277 rows in its log. Does anyone have any advice on how to investigate this and identify why the rows haven't been purged. The purged date on the table is older than the rest of the master tables. – Moz Jul 01 '13 at 16:35