See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist
A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick "Miller, Jay" <JayMiller@TDWater To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> house.com> cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask :). Thanks, Jared. Jay -----Original Message----- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but "what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value?" Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: > I have one huge table (takes up about 30% of the total database storage) > which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED > set to the defaults of 10 and 40 respectively. > > I occurred to me that I could probably free up a lot more space by > increasing the PCTUSED so that more blocks would be available to be written > to (since getting more storage for the server is a bureaucratic nightmare > here). > > So my question is, if I just raise the PCTUSED from 40 to, say, 75 would > all blocks that fall into the 40-75 range become available for inserts? Or > is it only after their next update or delete? > > Different sections of the docs seem to imply different things. The docs > say: > > A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE > statements for moving a block to the free list when the block has fallen > below that percentage of usage. > > This seems to imply that it won't be moved to the freelist until a delete > or update is done that affects that block. > > > But they also say: > > A higher PCTUSED increases processing cost during INSERTs and UPDATEs. > > This seems to imply that when it's looking to do the insert it might find > that it can insert to a block. > > > Anyway, why would a lower PCTUSED reduce processing costs during a DELETE > but a higher PCTUSED wouldn't increase processing costs during a DELETE. > That makes no sense. I'm befuddled. > > > TIA, > Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).