Re: Finding space-bound objects?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 30 Apr 2002 06:43:17 -0700
Message-ID: <178d2795.0204300543.1233287@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
> 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 -- Received on Tue Apr 30 2002 - 08:43:17 CDT