Re: Fragmentation level

From: AJ Allen <aallen20_at_ford.com>
Date: Mon, 07 Feb 2000 11:53:18 -0500
Message-ID: <389EF87E.2314BFFA@ford.com>

vs wrote:
>
> How I can evaluate the level of fragmentation in my segment or in my
> entire tablespace?
> Thanks,
> Victor
> slootsky_at_erols.com

Try this . . .
- - - - - - - C U T H E R E - - - - - - - - - - SET PAGESIZE 70
SET LINESIZE 85
SET NEWPAGE 0

SET VERIFY     ON
SET ECHO       OFF

SET UNDERLINE =
SET HEADING ON
SET FEEDBACK Off
SET LONG 1000 SET EMBED ON --SPOOL frag_report.lst

COLUMN sys_id    NOPRINT new_value system_id
COLUMN sys_date  NOPRINT new_value system_date
COLUMN sys_user  NOPRINT new_value system_user

COLUMN name      FORMAT  a17  TRUNC  HEADING 'Tablespace'
COLUMN blocks    FORMAT  99,999,999  HEADING 'Blocks'
COLUMN free      FORMAT  99,999,999  HEADING 'Free'
COLUMN pices     FORMAT  99,999      HEADING 'Pices'
COLUMN biggest   FORMAT  999,999     HEADING 'Biggest'
COLUMN smallest  FORMAT  999,999     HEADING 'Smallest'
COLUMN average   FORMAT  999,999     HEADING 'Average'
COLUMN Dead      FORMAT  99,999      HEADING 'Dead'

BREAK ON name

TTITLE Right  'Date: ' system_date   -
  skip Left   'Database: ' system_id -
       Center 'Tablespace Fragmentation'  -
       Right  'User: ' system_user   -
  skip Center '~~~~~~~~~~~~~~~~~~~~~~~~' -

  skip 2

REPFOOTER SKIP 3 CENTER '### ## END OF REPORT ## ###' BTITLE -
SKIP 2 RIGHT 'Page: ' format 99 sql.pno - SKIP 1 CENTER '### ## Confidential Information -- Dispose of properly ## ###'

SELECT  d.name                 sys_id,
        to_char(SysDate,'mm/dd/yyyy  HH24:MI:SS') sys_date,
        User                   sys_user,
        ts.name                name,
        tf.blocks              blocks,
        SUM(f.length)          free,
        COUNT(*)               pices,
        MAX(f.length)          biggest,
        MIN(f.length)          smallest,
        ROUND(AVG(f.length))   average, 
        SUM(DECODE(SIGN(f.length-5), -1, f.length, 0)) dead 
  FROM  v$database  d,
        sys.fet$    f, 
        sys.file$   tf, 
        sys.ts$     ts

WHERE ts.ts# = f.ts#
  AND ts.ts# = tf.ts#
GROUP
   BY d.name,

       ts.name,
       tf.blocks

/
--spool off

• - - - - - - - - - - - - - - - - - - - - - - - - - -- *------------------------------*----------------------------* | Andrew Allen | Against fools, the gods | | Process Leadership / PSL DBA | themselves contend in vain | | Dearborn, MI USA | -- author unknown | *------------------------------*----------------------------*

Received on Mon Feb 07 2000 - 10:53:18 CST