Re: Tablespace and Segment Frag.

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: Fri, 24 Jul 1998 01:35:57 GMT
Message-ID: <35bae367.6384198@172.16.7.252>

On Wed, 22 Jul 1998 22:37:51 -0700, Software Administrator <sanjeev_at_pacbell.net> wrote:

Hello,maybe you can try this:

/* file:FSFI.sql

   This script measures the fragmentation of free space    in all of the tablespaces in a database.    The formula is:

FSFI=

                     largest extents                 1
100 * sqrt(-----------------------) * -----------------------
                    sum all extents      (number of extents)^1/4

The ideal FSFI is 100 with no fragmentation, or at least over 30 */

select
tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) "FSFI"
from DBA_FREE_SPACE
group by tablespace_name;

Hope this help!!!

Violin

>Hello all,
>
> What is the quick and easy way to find out fragmentation for TB and
>its segments. I have been told to see two views.
>
>For Tablespace
> DBA_FREE_SPACE
>
>For Segments
> DBA_SEGMENTS
>
>
>What columns and values will tell if there is a fragmentation.
>
>Any help will help....
>
>Thanks,
>Sanjeev
Received on Thu Jul 23 1998 - 20:35:57 CDT