RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?

John,

Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued.

I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block.

This was on 8.1.7 on Linux.

It's in the archives if you care to look for it.

Jared

John.Hallas_at_vodafone.co.uk
Sent by: root_at_fatcity.com
 09/24/2002 09:08 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?

Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is
now a discussion on whether changes to a pctfree/pctused are retrospective.

I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist.
I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist.

I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future

John

-----Original Message-----
Sent: 24 September 2002 15:47
To: ORACLE-L_at_fatcity.com; John.Hallas_at_vodafone.co.uk

Are you sure about that John?

On Tuesday 24 September 2002 04:28, John.Hallas_at_vodafone.co.uk wrote:
> No, it is not retrospective.
> You are setting parameters to be used when the next extent is created.
> A better example is when setting next extent size to be different than
the
> existing extent size (dictionary managed tablespaces only).
> It does not alter all the existing extents it only works on the next one
> that is created.
>
> HTH
>
> John
>
> -----Original Message-----
> Sent: 24 September 2002 10:58
> To: Multiple recipients of list ORACLE-L
>
>
>
> Is the effect of modifying PCTFREE/PCTUSED immediate ?
>
>
> If I do an "ALTER TABLE <tablename> PCTFREE 99 PCTUSED1",
> does this take effect immediately, even for existing blocks.
> [If so, existing blocks would not get new rows inserted].
> Or is it effective only in new Extents ? In that case,
> existing blocks in existing Extents still use the old
> PCTFREE/PCTUSED parameters and keep re-entering the
> FreeList.
>
> Hemant K Chitale
> http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).