Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Free space fragmented

Re: Free space fragmented

From: Saar <sxmaxoz_at_us.oracle.com>
Date: Mon, 22 Jun 1998 15:11:50 -0700
Message-ID: <358ED6A6.FD81B8DA@us.oracle.com>


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.

>
>
> 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.

> 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.

> 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.

--

 __  _    _  __    _ _   _   _ ___  ______________________________
((  /\\  /\\ ||)  |\V/| /\\ /\\ >/  Principal Performance Engineer
_))//-\\//-\\||\ |||||//-\\\\//<_ Oracle Corporation Digital SBU
////////////////// Drop x's in email (spam) //////////////////////



Received on Mon Jun 22 1998 - 17:11:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US