Re: Detect Fragmentation of a tablespace

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: Fri, 24 Jul 1998 06:16:17 GMT
Message-ID: <35b9268e.23578059@172.16.7.252>

On Thu, 23 Jul 1998 18:31:56 GMT, poohland_at_hotmail.com 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

>Hi!
>
>Is there any script that can help me to detect the fragmentation of a
>tablespace in order to decide whether I should rebuild it or not?
>
>Thank you very much in advance
>
>Winnie Liu
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Fri Jul 24 1998 - 01:16:17 CDT