TRUNCATE and NEXT_EXTENT

[svk]

Here is what happened :

A table was mistakenly specified to have 2K next rather than 2M.
I started a data load and got ORA-01631-Max extents reached.
Checked the things and changed the NEXT to 2M. Successfully.
Truncated the table and restarted the data load only to get the ORA-01631 error again !!
Checked the NEXT for the table and it was back to 2K !!

When I tested it (806/817), I noticed that when the table is occupying extents equal to MAXEXTENTS value and you change its NEXT and issue a TRUNCATE statement, the NEXT gets the old value again !! TRUNCATE doesn't do anything to NEXT value if the table is NOT occupying MAXEXTENTS extents.

Checked on MetaLink and there is something related to this there (Bug#1546221).

Accepted that this is a documented "feature" (as mentioned in the MetaLink note), but why in the world should TRUNCATE touch the storage values of a table ?

[BV1963]

Truncate has to touch storage settings for a table because the way it works. It wipes out information about all extents that belong to the table except the initial one, or another words resets high water mark. This is a reason why it is very fast.
However, if you fo not want it happend you could say
This will keep current storage settings for a table unchanged.Not sure if it takes longer than an usual truncate though.

[gpsingh]

My theory is

U have reached maxextents, so table cannot be extebded beyond that
Changing next extent is in effect only for the extents beyond the last used but since in your
case there is no extent beyond last extent , the next extent does not get into effect.

So it is not truncate which is causing it but the fact that next extent change only affects the extents
beyond the last extent.

Try this
after u reach maxextents, truncate and then change next extent and re import.
It should work.

[svk]

BV1963,

I guess, the important part of you post has got truncated out....Can you post again please ?

[BV1963]

Truncate has to touch storage settings for a table because the way it works. It wipes out information about all extents that belong to the table except the initial one, or another words resets high water mark. This is a reason why it is very fast.
However, if you fo not want it happend you could say

TRUNCATE TABLE T1 REUSE STORAGE;




This will keep current storage settings for a table unchanged.Not sure if it takes longer than an usual truncate though.