Re: truncate command 8.1.7. locally managed tablespaces

"Bob" <where_was_you_at_btinternet.com> wrote in message news:733fcc55.0305191125.4d95064f_at_posting.google.com...
> The truncate command on non uniform locally managed tablespaces leaves
> the next extent and pct_increase intact instead of taking the default
> values from the tablespace as for dictionary managed.

I don't think I ever tested the use of PCTINCREASE with autoallocated locally managed tablespace in 8i.

Two reasons: autoallocate was strongly not recommended at the time, and PCT_INCREASE was such a stupid invention way back in Oracle 7 or before, that you'd have had to be criminally insane to want to combine the two features!

>Next time
> records are inserted beyond the initial extent size then it uses the
> next extent size for its calcxulation worst it then multiplies it by
> 1.5 (50% pct_increase shrink wrapped oracle applications). This was
> noted in a migration program that truncated all old rows for a
> previous run before importing more and eventually reached a 12.9G next
> extent before space could not be allocated after about 23 such
> migrations.
> Does anyone know if this is a bug or a feature all the stuff I've read
> says it ignores both next extent and %increase.

All I can tell you is that in 9iR2, after a quick test:

I created a table with PCTINCREASE of 50%. As per the normal autoallocate algorithm, I got 16 extents of 64K, and the 17th was of 1MB, indicating that the PCTINCREASE setting was completely ignored.

I then kept inserting, and got repeated 1MB extents (so PCTINCREASE was still being ignored). If I'd kept going, I'm sure I would have eventually (around the 200th extent or therabouts) shot up to 8M extents. Which is all just perfectly standard autoallocate behaviour.

I then truncated the table.

I then re-inserted some rows... and I got 16 64K extents, before it started allocating the 1MB extents.

Therefore, if as you report (and I don't doubt it) 8i 'remembers' the extent sizes is was up to before the truncate, so that the new set of extents start off at the enormous size they had previously reached, then that is certainly not the behaviour in 9i.

All I can suggest is that you don't use autoallocated LMT until you get to 9i (where it seems reasonably sound as an allocation method), and that you should have stopped using PCTINCREASE some time in version 7.

Regards
HJR Received on Tue May 20 2003 - 07:08:07 CDT