Usually it is not possible to shrink tables which are used by on commit materialized views.<\/p>\n
The result is an ORA-10652 “Object has on-commit materialized views” error, for which in action section nothing is suggested.<\/p>\n
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.
\nThe 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.<\/p>\n
This gives following procedure: 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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-11495","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\n
\nalter table table_name enable row movement;
\nalter materialized view materialized_view_name refresh on demand;
\nalter table table_name shrink space;
\nexec dbms_mview.refresh('materialized_view_name');
\nalter materialized view materialized_view_name refresh on commit;
\nalter table table_name disable row movement;
\n<\/code>
\nNote: 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.
\nThe 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.<\/p>\n","protected":false},"excerpt":{"rendered":"