MON - Tablespace Fragmentation

MON - Tablespace Fragmentation

What is it?
------------
Tablespace fragmentation occurs when the fragments of available FREE SPACE are 
less than the NEXT EXTENT of active (hot) segments.  Active segments are the 
segments that extend at least once a day (OLTP) or once every data load (DSS).

What to do?
------------
I do not recommend tablespace reorganization unless the fragmentation is 
extremely severe, instead I recommend careful alteration of active segment's 
storage parameters to fill in the free space fragments.

Automated Detection
--------------------
     
   FusionCode Event
   ----------------
     
   Event      : TABSP_FRAG
   Name       : Tablespace Fragmentation
   File       : SEEDMON/tbsfrag.sh
   Agent      : Local
   Collection : No
   
      Performs tablespace analysis looking for fragments of
      free space that are potentially wasted.  The goal here
      is to find free space fragments that are smaller then
      the next extent of "active" segments [segments that are
      either extending or are large].  Active segment list is
      built using collection from DBA_SEGMENTS that you can
      control using SEG% parameters (See below):
     
           owner NOT IN ( <SEG_OWNER_EXCLD> ) AND
           (extents > <SEG_EXTENTS> OR bytes/1024/1024 > <SEG_MBYTES>)
     
      To fix this issue you have two options:
         1. Reorganize tablespace.
         2. Alter the <NEXT_EXTENT> of any of the
            active segments to fill up the space.
     
      REPORT ATTRIBUTES:
      -----------------------------
      count of fragments
      fragment size (KB)
      total size [cnt*fragment_size](KB)
      tablespace name
     
      PARAMETER       DESCRIPTION                             EXAMPLE
      --------------  --------------------------------------  --------------
      FRAG_CNT        number of fragments threshold           30
                      DEFAULT=10
     
      TABSP_EXCLD     tablespace name list to exclude         'RBS','SYSTEM'
                      used in NOT IN (<LIST>) should
                      include single quotes and commas
                      DEFAULT='RBS'
     
      SEG_OWNER_EXCLD owner list to exclude                   'SYS','SYSTEM'
                      used in NOT IN (<LIST>) should
                      include single quotes and commas
                      DEFAULT='SYS','SYSTEM'
     
      SEG_EXTENTS     number of extents threshold             100
                      DEFAULT=50
     
      SEG_MBYTES      segment's MB size threshold             500
                      DEFAULT=200(mb)

Example of (TABSP_FRAG) event trigger output

.