Yes you can move within the same tablespace. I had to do this when one of the other developers created a table with the default pctfree. Updates were causing rows to chain, so I issued the alter table move command with a new pctfree. Rebuilt the indexes and analyzed the table again and so far everything is working great.
-- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 9/26/2002 12:33 PM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > >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). > > -- 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).
