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
adavey_at_competitrack.com
212-604-0200 x106
On 9/26/2002 12:33 PM, Hemant K Chitale <hkchital_at_singnet.com.sg> 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
>>adavey_at_competitrack.com
>>
>>
>>
>>On 9/25/2002 11:38 AM, Hemant K Chitale <hkchital_at_singnet.com.sg>
>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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>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).
>> >
>> >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: hkchital_at_singnet.com.sg
>> >
>> >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: Alan Davey
>> INET: adavey_at_competitrack.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).
>
>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: hkchital_at_singnet.com.sg
>
>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: Alan Davey
INET: adavey_at_competitrack.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).
Received on Thu Sep 26 2002 - 11:53:37 CDT
