Infrastructure at your Service

Michael Hein

How to shrink tables with on commit materialized views

Usually it is not possible to shrink tables which are used by on commit materialized views.

The result is an ORA-10652 “Object has on-commit materialized views” error, for which in action section nothing is suggested.

There is a workaround for this error: Convert all materialized views which rely on your table to be shrinked from on-commit to on-demand views. Application must tolerate that the affected materialized views are not updated during shrinking space of the table.
The affected materialized views must be queried in dba_mviews and the sql query must be checked whether table to be shrinked is used by this materialized view.

This gives following procedure:
alter table table_name enable row movement;
alter materialized view materialized_view_name refresh on demand;
alter table table_name shrink space;
exec dbms_mview.refresh('materialized_view_name');
alter materialized view materialized_view_name refresh on commit;
alter table table_name disable row movement;

Note: For alter table enable row movement and alter table shrink space table must be accessible, otherwise locks on the table may cause delays or errors.
The statements alter materialized view on demand, exec dbms_mview.refresh and alter materialized view on commit must be executed for every materialized view which uses the table to be shrinked.

Leave a Reply

Michael Hein
Michael Hein