Re: Finding space-bound objects?

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3CCEE2A5.3D07_at_yahoo.com>...
> Mark D Powell wrote:
> >
> > 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
> > > INITIAL_EXTENT and NEXT_EXTENT values of 16384 and 16384 respectively,
> > > won't the extents be allocated with these sizes:
> > >
> > > 16384
> > > 16384
> > > 24576
> > > 36864
> > > 55296
> > > etc...
> > >
> > > I appreciate that these might not be the exact values, as presumably
> > > the real values would have to be multiples of the block size, but you
> > > can see the principle.
> > >
> > > Any comments or input very welcome!
> > >
> > > 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.
> >
> > I recommend you look at conversion/migration to using locally managed
> > tablespace, but I understand that if you have an existing system
> > conversion may not be desirable as the database is currently
> > configured.
> >
> > Prior to LMT and inline views I wrote two plsql scripts to accomplish
> > this task. They are a little long to post but I will email them on
> > request.
> >
> > But here is a query that will tell you how many extents equal to the
> > largest extent, which any object in the tablespace will request, are
> > available. The script will work for version 7.3+:
> >
> > COLUMN tablespace_name FORMAT a20 heading 'Tablespace Name'
> > COLUMN extents FORMAT 99999 heading 'Available|Extents'
> >
> > select f.tablespace_name
> > ,sum(floor(nvl(f.bytes,0)/(s.MEXT))) extents
> > from sys.dba_free_space f
> > ,( select tablespace_name, max(next_extent) as MEXT
> > from sys.dba_segments
> > group by tablespace_name
> > ) s
> > where f.tablespace_name = s.tablespace_name(+)
> > group by f.tablespace_name
> > /
> > Available
> > Tablespace Name Extents
> > ==================== =========
> > AHQTS01 7213
> > FSWIL01 136
> >
> > This SQL will not tell which objects may fail but it does point you to
> > tablespaces that are running low on space and works very well with
> > LMT's but will also work on dictionary managed tablespaces. Warning
> > 100% full tablespace have no free space and hence do not show.
> >
> > HTH -- Mark D Powell --
>
> Its worth noting that the "can I extend?" question is 'unanswerable' in
> the case of auto-allocate lmt's anway
>
> hth
> connor

My first thought when I read your (email) reply was true, but then the question hit me: what does Oracle set the next_extent value to for an object in an autoallocate tablespace? I do not have an autoallocate tablespace that I can check.

Based on the size of the largest extent size allocated in the tablespace you can still make a reasonable guess to when trouble is definitely approaching space wise. But that jump from 8M extents to 64M extents could present a problem.

If you wanted to I imagine you could write some plsql based on the current, but subject to change, allocation pattern as noted by Howard J Rogers in an earlier thread on this subject, but it seems like a lot of work to try to predict running out of room when using uniform extents makes it so much easier.

Conner, I believe I am in basic agreement with you in principle, but your statement is a little too absolute. As a DBA it is our job to predict when a file is necessary to be added to the tablespace (or an object migrated to another tablespace) so an estimate has to be made. But you are right about it being difficult for an autoallocate tablespace.

• Mark D Powell --