Re: Free space fragmented

Add another datafile to that tablespace and go on. When you upgrade to 7.3, you can coalesce your freespace.

If it really bugs you, do a full database export with compress=y. Drop and recreate all your tablespaces, then reimport. Easier said than done, but this will clean everything up.

To avoid fragmenting your indexes, use an initial size that will accomodate your projected index size. As you've seen, you will need one free extent that's at least this size available. I would also make the next extent big, so if it has to extend, it doesn't have to do it often. PCTincrease 0 is a good idea in this case.

Studies have shown that a little fragmentation, say less than 10 extents, doesn't really impact performance that much.

Roy Varghese wrote:

> Hi guys,
>
> I have run into a typical problem, please suggest
> the best way out.
> We are running PeopleSoft databases, and some of
> the free tablespaces
> have got very fragmented. In fact I find that I
> have free space in the
> tablespace, but its so fragmented, I am unable to
> allocate it. Especially
> the tablespaces containing indexes.
> Whats the best way to coalesce all the free space.
> We are running
> Oracle 7.2.3 and it doesnt support ALTER
> TABLESPACE COALESCE,
> but even the COALESCE command wont work on
> non-contiguous
> free spaces.
>
> Also is there any way to preallocate extents to an
> index while creating it.
> Something like the equivalent of ALLOCATE EXTENT
> used for tables.
> For example, say I have the following free extent
> sizes
> 1000 K
> 2000 K
> 3000 K
> And I would like to create an Index of approx 6000
> K in size. How can
> I fit the index in the free extents available
> (i.e. with 3 or less fragments) ??!!!
>
> Any help appreciated. Thanks in advance !!!!
>
> Roy Varghese
Received on Mon Jun 29 1998 - 21:08:06 CDT