Re: Fragmentation level

From: AJ Allen <aallen20_at_ford.com>
Date: Mon, 07 Feb 2000 11:56:01 -0500
Message-ID: <389EF921.B9994D0C@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

Or this . . .
- - - - - - - C U T H E R E - - - - - - - - SET TERMOUT OFF SET PAGESIZE 40
SET LINESIZE 90
SET NEWPAGE 0

SET VERIFY     OFF
SET ECHO       OFF

SET UNDERLINE =
SET HEADING ON
SET FEEDBACK OFF
SET LONG 1000
SET EMBED ON

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 rpt_notes   FORMAT a100         HEADING 'Notes'

COLUMN file_id     FORMAT 999          HEADING 'File| ID'
COLUMN owner       FORMAT a30   TRUNC  HEADING 'Owner'
COLUMN object      FORMAT a32   TRUNC  HEADING 'Object'
COLUMN block_id    FORMAT 9999999      HEADING 'Block|ID'
COLUMN blocks      FORMAT 999,999,999  HEADING 'Blocks'

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

  skip Left 'Tablespace: ' ts_name -
  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

  FROM v$database d
;

--SPOOL ts_map.lst

SELECT  'free space'      AS owner,
        ' '               AS object,
        file_id,
        block_id,
        blocks

  FROM dba_free_space
 WHERE tablespace_name = UPPER('&ts_name')  UNION
SELECT owner,

        segment_name,
        file_id,
        block_id,
        blocks

  FROM dba_extents
 WHERE tablespace_name = UPPER('&ts_name')  ORDER
    BY 3, 4
;

SELECT 'This listing shows the distribution of freespace and objects within the tablespace. With
it you can identify objects that are barriers between free extents. This will allow you
to identify objects which, if relocated, will allow free space to be coalesced into large
contigous extents. Before allocating more file space to a tablespace you should review
this report to see if objects should be relocated so that existing free space can be
recaptured.'

    AS rpt_notes
  FROM DUAL;
--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:56:01 CST