Re: PCTFREE & PCTUSED

On Fri, 21 Aug 1998 23:23:16 -0500, Arthur Merar <amerar_at_unsu.com> wrote:

>What I do not understand is something like this: If PCTFREE is 20% and
>PCTUSED is 40%, Can inserts be done if the block is 50% filled???
>According to PCTFREE it can, but according to PCTUSED no nre inserts can
>be done.

The answer is both yes and now. It is yes if the block has never crossed the 80% threshold. It is no if the block at one time crossed the 80% threshold, and data has been deleted.

Here's what happens, using your figures for an example. As a block fills with data, Oracle watches to see when that block becomes 80% full. Until that happens, the PCTUSED value has no meaning. You can insert all the data that you want. Once you hit 80% full, the PCTFREE parameter stops new rows from being added. Note however, that existing rows can be changed. Once 80% has been hit, and PCTFREE stops new rows from being inserted, PCTUSED begins to have meaning. The block is marked somehow to disallow inserts. You can delete data, but the block will still not allow inserts. Every time you delete data from the block, Oracle checks to see if the used space has fallen to less than 40% (PCTUSED). When that happens, the insert restriction is removed.

The whole point of this is to prevent a block from repeatedly thrashing between the state of being able to hold a new row and the state of not being able to hold a new row.

regards,

Jonathan Gennick Received on Sat Aug 22 1998 - 17:25:30 CDT