Is it possible to minimize the time needed to move HWM on an table with Alter table?
In an Prod Database we try to shrink an table from 200GB to 30Gb the first step is successful after 15h
alter table DATAWIZARD.DW_LOG shrink SPACE compact cascade;
the second step was started in off peek hours but after 1 min we got blockers after 10 minutes we killed the process because no one could work anymore.
alter table DATAWIZARD.DW_LOG shrink SPACE cascade;
Repeating the first step takes about 2h
We tried the same on a Test DB with stopped application,
alter table DATAWIZARD.DW_LOG shrink SPACE cascade;
immediately after the first step took more than one hour.