Re: Rebuild table

From: Adric Norris <>
Date: Wed, 22 Jun 2011 09:03:32 -0500
Message-ID: <>

I encountered something similar recently, when compressing numerous tables in an Exadata environment ( Most of the time (90-95% guesstimate) the rebuilt segments would be placed toward the beginning of the datafile, but occasionally one would reside near the end... we never did figure out why Oracle choose to do that (there was *plenty* of freespace toward the beginning).

Our scripts were setup to begin moving tables/partitions based upon the position of the final extent, so watching for changes in the reclaimable space brought the issue to light rather quickly. Since the tablespace in question contained only archive data, of which we wanted to compress the vast majority, we ended up simply creating a new tablespace and moving everything there.

On Tue, Jun 21, 2011 at 20:47, Michael Rosenblum <>wrote:

> Extra 2c. I would suggest as a possible solution already mentioned ALTER
> TABLE ... MOVE, but fired twice:
> - first, move everything to completely different tablespace
> - second, move it back where the data belongs
> - rebuild indexes
> To be fair, I don't exactly remember the real case, but sometimes ago
> (Oracle 10g R2 on Win32) this double-jump solved my space allocation
> problem, while single operation was not not giving me what I wanted.
> Regards,
> Michael Rosenblum

Received on Wed Jun 22 2011 - 09:03:32 CDT

Original text of this message