Re: locally managed tablespace - autoallocate

<premmehrotra_at_hotmail.com> wrote in message news:1102557907.163709.161940_at_z14g2000cwz.googlegroups.com...
>I am using Oracle9i on HP UNIX 11i. I have a locally managed tablespace
> with auto allocate extent size (instead of uniform size). Which data
> dictionary view will show me what is the size of
> next extent for a table. I know next extent size is determined by an
> internal Oracle algorithm, but where in the data dictionary it is
> shown.
>
> I want to write a script to find whether my datafile has enough space
> for next 2 extents for any table in the tablespace. I have scripts to
> find this in dictionary managed tablespace, but struggling how to
> modify them to LMT with autoallocate option.
>

Hi Prem

May I suggest you're solving your problem the wrong way.

You're trying to prevent the scenario whereby a table/segment can't grow because the tablespace is too full. But isn't waiting until a *single* table has room for only two extents leaving things a little too late ? Typically, you may have many hundreds of segments within a tablespace. What happens if for example 3 different tables within a tablespace grow at a similar time. So much for your script !!

A far better solution is to look at the tablespace as a whole and if the freespace is < 10% or < 5G (or any value you feel appropriate), whichever is lower, look at increasing the tablespace. Or keep weekly records/snapshots of space used within tablespaces and if you have less than x weeks remaining based on average/last weeks space usage, increase the tablespace as appropriate. Such scripts are relatively easy to write.

That way it doesn't really matter what happens at the micro level, you've given yourself plenty of time and a reasonable margin of exceptional usage growth to take appropriate pre-emptive action way before a problem occurs.

I like to ensure there's sufficient space for at least a month's typical growth so that I can take long breaks from sites with a clear conscious :)

Cheers

Richard Received on Thu Dec 09 2004 - 04:59:22 CST