Database Administration (MOSC)

MOSC Banner

Is it possible to minimize the time needed to move HWM on an table with Alter table?

edited Feb 11, 2021 9:49AM in Database Administration (MOSC) 4 commentsAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center