Re: Finding space-bound objects?

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

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."