Re: Finding space-bound objects?

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0204300543.1233287_at_posting.google.com>...
> tim.kearsley_at_milton-keynes.gov.uk (Tim Kearsley) wrote in message news:<725736ef.0204300252.2ce88692_at_posting.google.com>...
> > Hi all,
> >
> > I've looked at several scripts which purport to produce a list of
> > tables, indexes etc. which can't extend within the tablespace, because
> > the largest contiguous piece of free space is less than the
> > NEXT_EXTENT value for the segment.
> >
> > However, unless I'm not seeing something here, this is too simplistic
> > for the real world isn't it?
> >
> > What happens if the segment has a PCT_INCREASE value non-zero? Surely
> > in that case the next extent it wants to create will be the size of
> > the last extent it created * (1 + PCT_INCREASE/100) won't it? Or have
> > I misunderstood the meaning of PCT_INCREASE or the way it is
> > implemented?
> >
> > For example if the table BIGTAB has a PCT_INCREASE value of 50 and has

<Snipped>

> > Regards,
> >
> > Tim Kearsley
> > Database Manager,
> > Milton Keynes Council
>
> Tim, the value in the next_extent column of dba_tables is the size
> that the next allocated extent will attempt to use so you can use it
> to find objects that will fail on their next attempt to extend.
> PCTINCREASE is already included.

... and don't forget about autoextendable datafiles. You don't want to get a page at 4am (seems to be a rush hour for such things :) just because your script doesn't know about them. Others already mentioned LMT where rules are different.

Even if script checks all these it's still too simplistic for the real world. Two segments are about to allocate the next extents, there is enough free space for any _one_ of them but not for both. Will your script alert you? Can it alert you that a segment won't be able to allocate n-th next extent so you would have time to buy/install additional hard drives?

Sure you can code this in but you'll probably get a lot of false alerts. You need historical data (growth rates) to make it more precise. Also you should be aware about coming changes in business which may change historical trends.

Do you need such a script? Yes, unless you have a tool (OEM, Patrol, etc) which does it for you. Your goal as a DBA is not to get to the point when you receive such alerts (hint: disk space is cheap :).

The check scripts are like airbags in the cars - we have them installed but we drive safely enough not to really use them :)

Regards,
Igor

<Snipped>

> HTH -- Mark D Powell --
Received on Wed May 01 2002 - 08:25:17 CDT