Re: Truncate changes NEXT allocation?

From: Jurij Modic <jmodic_at_src.si>
Date: Wed, 12 May 1999 22:12:11 GMT
Message-ID: <3739f75c.6150089@news.siol.net>

On Wed, 12 May 1999 21:51:25 +1000, "Nuno Souto" <nsouto_at_nsw.bigpond.net.au> wrote:

>As the title says.
>
>Someone I know maintains this is true. I've tried it and it doesn't.
>Is there any version of ORACLE where this may happen?
>
>Basically, if a table has eg INITIAL 1M NEXT 1M, then you
>change via ALTER to NEXT 10M, this guy maintains that
>a truncate will reset NEXT to 1M. I've verified that
>in normal conditions it doesn't, so I need to know of any
>"abnormals", if any.
>
>TIA for any info on this. Go via e-mail if your newsfeed
>is not very reliable, I find it easier.

IMHO none of the previous answers explains exactly how TRUNCATES behaves regarding the setting of the NEXT storage parameter after truncating the segment. And yet, it is clearelly stated in the SQL Reference Manual (describing command TRUNCATE):

<quote>
Note: When you truncate a table, the storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation.
</quote>

You must read the above note very carefully to see that the TRUNCATE behaves exactly as described. Here are some example scenarios:

You have a table of 1 extent allocated (EXTENT_ID=0). If you truncate it, the NEXT parameter will remain unchanged, as the truncate did not "delete" any extent from the table.
You have a table of more than 1 extent allocated, MINEXTENT set to default value of 1. After truncate you'll have 1 extent left in a table and the NEXT will be set to the size of your second (EXTENT_ID=1) extent prior to truncation, regardless of the setting of the NEXT parameter in effect when truncation took place.
You have a table of 10 extents with MINEXTENT set to 5. If you truncate it, you'll have a table of 5 extents, and the parameter NEXT will be set to the size of your former 6th extent (EXTENT_ID=5) regardless of your setting of the NEXT prior to truncation.

>--
>Cheers
>Nuno Souto
>nsouto_at_nsw.bigpond.net.au
>http://www.acay.com.au/~nsouto/welcome.htm

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed May 12 1999 - 17:12:11 CDT