Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Free space fragmented
Saar wrote:
> 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.
>
> make sure you have PCTINCREASE set to 0
otherwise it will introduce some
> strangesize extents.
>
> >
PCTINCREASE IS set to zero! But still I do have strange extent sizes : )
> >
> > Also is there any way to preallocate extents
to an
> > index while creating it.
> > Something like the equivalent of ALLOCATE
EXTENT
> > used for tables.
>
> when you create the index you can specify
MINEXTENTS which isa storage
> parameter good for all objects. The allocate
extent command
> is used for indexes & tables that are already
created.
True. But MINEXTENTS will allocate equal sized
extents only, as specifiedby
NEXT in the storage clause
>
>
> > 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) ??!!!
> >
>
> in this case you'd go with 3 alter index X
allocate extent (datafile
> '..' size xxM)commands.
I could do that, but the table is already so big
that when I do aCREATE
INDEX, the index size is 6000 K, in other words,
the
CREATE INDEX does not complete in order for me to
do an ALTER INDEX.
>
>
> > Any help appreciated. Thanks in advance !!!!
> >
> > Roy Varghese
>
> Since this is just index tablespace I'd just
drop them all and recreate
> them again.
> you do need to get a hold of an event that can
do the coalescing in V7
> as well, contact Support for
> that.
>
> Saar.
I will get in touch with Support for the coalesce.
Any solutions to the
otherproblem ? I would like to avoid recreating
all indexes if
possible....short
on downtime : )
Thanks for the tips!
Roy
>
>
> --
> __ _ _ __ _ _ _ _ ___
//////////////////////Received on Mon Jun 22 1998 - 17:20:37 CDT