Re: Packing or freeing datafiles?

From: Snewber <snew_at_snew.com>
Date: Mon, 09 May 2005 13:05:43 +1000
Message-ID: <d5mk27$2ndq$1@bunyip2.cc.uq.edu.au>

It depends on the conditions of the objects. The DBA has to make the decision on whether to rebuild the objects or not.

But here is scenario where it has worked for me.

The database is Oracle 9.2.0 with tablespaces that have

EXTENT MANAGEMENT LOCAL UNIFORM SIZE
SEGMENT SPACE MANAGEMENT AUTO You have a table that is currently around 15 million rows, in it's own tablespace, and it is about 8Gb in size. The table also has about 12 indexes on it and these indexes are in another tablespace. The size of all the indexes, for the above table, is also around 8Gb. A large data purge is done reducing the number of rows back down to about 1 million.

You want to free up all of the space that is now available due to the table purge. You move the table once in the same tablespace (ideally you could move it into a new tablespace, but this means changing cloning templates), so it is moved to the end of each datafile in the tablespace. The HWM increases and the tablespace possibly autoextends. Now, although the table is now about 1/15th it's size, the tablespace can't be resized because the HWM is still too high. So, if you do another table move the table will be moved to the beginning of the datafiles (or that is what I have experienced)

Now you could simply do an index rebuild, but I have found that blocks may still exist up at the HWM. So if you rebuild the indexes twice (this is a lazy way of doing it but very safe), once into another tablespace and then back again then you are guaranteed of freeing up the space in the original tablespace. If you wanted to you could just leave them in the new tablespace, but existing templates would need to be changed if you clone your databases.

Sybrand Bakker wrote:
> On Thu, 05 May 2005 10:19:01 +1000, Snewber <snew_at_snew.com> wrote:
>
>

>>If the table starts at the beginning of the datafile then moving it once 
>>won't work, but if you move it twice then it should definitely work.
>>
>>Also, in regards to indexes, I've found that you should rebuild them 
>>into another tablespace and then maybe rebuild them back into the 
>>original tablespace if needed, however leaving them in the new 
>>tablespace would be quicker if that isn't a problem for you.
>>
>>Ideally you should just reverse engineer your indexes, then drop them 
>>and recreate them (if possible). This is harder than moving them twice, 
>>but twice as quick. I guess it depends on whether you need to have them 
>>online at all times.

>
>
> This advice is absurd.
> There is no need to rebuild indexes twice, let alone drop them and
> recreate them. You won't gain anything by doing so.
> Also it is absurd (and you also don't provide any proof) you should
> move tables twice.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sun May 08 2005 - 22:05:43 CDT