From: "spencer" Subject: Re: pctfree and pctused Date: 2000/03/03 Message-ID: #1/1 References: <89jtf8$5cm$1@nnrp1.deja.com> <38BF6A06.C9C3D73@gtt.es> X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Complaints-To: abuse@swbell.net X-Trace: news.swbell.net 952083233 208.190.221.157 (Fri, 03 Mar 2000 05:33:53 CST) Organization: SBC Internet Services X-MSMail-Priority: Normal NNTP-Posting-Date: Fri, 03 Mar 2000 05:33:53 CST Newsgroups: comp.databases.oracle.server "José Antonio Morcillo Valenciano" wrote in message news:38BF6A06.C9C3D73@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. > > wrote in message news:89jtf8$5cm$1@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. > >