Well, yes, that is one option. Can I issue an ALTER TABLE <table> MOVE to the same tablespace, I wonder.
Thanks Hemant At 08:23 AM 25-09-02 -0800, you wrote: >Hemant, > >If I understand your question correctly, trying using the alter table move >command and specify new values for pctfree and pctused. This should >affect existing blocks. Make sure to rebuild any indicies. > >HTH, >-- >Alan Davey >[EMAIL PROTECTED] > > > >On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > > > >Let me clarify my original question. > > > >I do not expect the FreeList for a table to get updated instantaneously > >after I change the PCTFREE/PCTUSED. > >What I meant by "is the effect ... immediate" is that do the > >new values come into play immediately -- even for existing blocks. > > > >Suppose I have a table where PCTFREE was high (40) > >and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], > >there would have been about 60% [100-40] usage in the block -- > >these could mean a large number of rows. > >Now, I want to reduce the number of rows in a block -- the > >particular table is a "hot" table where some blocks become > >"very hot" spots [extremely high rate of updates to existing rows, > >updates which do not increase the size of existing rows]. > >My "cache buffer chains latch" contention is high. > >I further introduce the possibility of deletes [e.g. a purge job > >running daily]. Because PCTUSED is high, and not very many > >rows in a block get deleted at each purge, the block is unlikely > >to come into the FreeList early. It would be a number of days > >before enough rows are deleted from the block. > > > >Therefore, to reduce the contention for the "hot blocks", I decide > >to have only 1 row in each block. Normally, with a *NEW* table, > >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. > >But if I have a large number of blocks in a few extents created when > >PCTFREE was 40 and PCTUSED 50 or 60. When would these > >existing blocks start behaving as if they were created with > >PCTFREE 99 and PCTUSED 1 ? > >How about new blocks ? Would new [empty] blocks in existing > >extents immediately behave such that they allow only one row > >per block ? Or would only new blocks in new extents take > >the PCTFREE 99 and PCTUSED 1 attributes ? > > > >Hemant > > > > > > > >At 03:53 PM 24-09-02 -0800, you wrote: > >>I replied too soon earlier, I think. > >> > >>Yes, what you state is correct. > >> > >>Jraed > >> > >> > >> > >> > >> > >> > >>[EMAIL PROTECTED] > >>Sent by: [EMAIL PROTECTED] > >> 09/24/2002 09:08 AM > >> Please respond to ORACLE-L > >> > >> > >> To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >> 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: [EMAIL PROTECTED]; [EMAIL PROTECTED] > >> > >> > >> > >>Are you sure about that John? > >> > >>On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > >> > >>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: [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 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: [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). > > > >Hemant K Chitale > >My web site page is : http://hkchital.tripod.com > > > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Hemant K Chitale > > INET: [EMAIL PROTECTED] > > > >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: [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: Alan Davey > INET: [EMAIL PROTECTED] > >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: [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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] 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: [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).
