Re: pctfree and pctused

"José Antonio Morcillo Valenciano" <jamv_at_gtt.es> wrote in message news:38BF6A06.C9C3D73_at_gtt.es...
> Sorry for my newbie question but could anyone explain easly
 what is pctfree and
> pctused!
>

PCTFREE and PCTUSED are parameters set in the STORAGE clause for a table. If not specified for the table, then the values are taken from the DEFAULT STORAGE clause for the tablespace.

When rows are inserted to a table, Oracle locates a block from the "freelist"... which is basically a list of blocks that have free space available for new rows.

When the percentage of available free space in a block drops below the PCTFREE threshold, Oracle will not insert any more new rows into the block, and the block is removed from the "freelist". The remaining free space within the block is reserved for expansion of existing rows in the block.

As rows are deleted from a database block, more space becomes available, but Oracle will not make the block available for new inserts (that is, put the block back onto the "freelist") until the percentage of space used in the block falls below the PCTUSED threshold.

To avoid a potential performance problem with Oracle flipping blocks onto and off of the "freelist", you want to make sure that the sum of PCTFREE and PCTUSED is less than 100.

> thank you!
>
> dgpare wrote:
>
> > The 20% Pctfree would be used for future inserts if you left
 it that way,
> > and in Oracle pctfree and pctused can not add up to 100. Try
 pctfree 0 and
> > pctused 99 if you are sure there will be no updates to the
 table. However if
> > you do this and find you are updating the table you will run
 into row
> > migration which is a new ball of wax.
> > <ewong_at_engineer.com> wrote in message
 news:89jtf8$5cm$1_at_nnrp1.deja.com...
> > > Hello,
> > >
> > > I have a huge table that only being inserted but never
 updated.
> > > Currently pctfree is 20 and pctused is 40. I would like
 to save some
> > > space by changing them to pctfree 0 pctused 100. My
 questions are:
> > >
> > > 1. Is the 20% free space going to be reuse for future
 insert?
> > >
> > > 2. How come I can't set pctused to 100? It gave me an
> > > ORA02211.
> > >
> > > Thanks,
> > >
> > > ewong
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
>
>
Received on Fri Mar 03 2000 - 00:00:00 CST