Rem Filename   : ts_fragmentation.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem Creation   : 18-Sep-1998
Rem RDBMS      : Oracle V8.x.x
Rem
Rem Modification history
Rem --------------------
Rem 18-Sep-1998  Initial creation.
Rem 19-Dec-2002  Rewritten.
Rem
Rem ==========================================================================================================
Rem
Rem Description : Lists the fagmentation per tablespace.
Rem
Rem Sample output
Rem -------------
Rem
Rem ----------------------------------------------------------------------------------------------------------
Rem Database              : GLORYETL on server beahs661640
Rem Current date and time : 19-DEC-2002 14:53:34
Rem ----------------------------------------------------------------------------------------------------------
Rem 
Rem Tablespace                >=500M >=200M >=100M >=50M >=20M >=10M >=5M >=2M >=1M >=512K >=256K >=128K <128K
Rem ------------------------- ------ ------ ------ ----- ----- ----- ---- ---- ---- ------ ------ ------ -----
Rem ...
Rem STAG_FS_INDEXES                       1            3     5     6
Rem STAG_FS_TABLES                               1           1
Rem SYSTEM                         1                                              2      1      1           11
Rem TEMP
Rem USER_INDEXES                                 2     1     1     1              2      1
Rem USER_TABLES                    1                   1                8   15   12      8     18     26    57
Rem XFORM_INDEXES                                      4     7    16   32   16   18      5      6      6    17
Rem XFORM_TABLES                          3      1     3     4    15   26   10   15      6      1      4     7
Rem ...
Rem ==========================================================================================================


SET TRIMSPOOL ON
SET PAGESIZE  100
SET FEEDBACK  OFF
SET VERIFY    OFF

@@header_dbinfo 109 DC

COLUMN Tablespace HEADING "Tablespace" FORMAT A25
COLUMN more500m   HEADING ">=500M"     FORMAT B9999
COLUMN more200m   HEADING ">=200M"     FORMAT B9999
COLUMN more100m   HEADING ">=100M"     FORMAT B9999
COLUMN more50m    HEADING ">=50M"      FORMAT B9999
COLUMN more20m    HEADING ">=20M"      FORMAT B9999
COLUMN more10m    HEADING ">=10M"      FORMAT B9999
COLUMN more5m     HEADING ">=5M"       FORMAT B9999
COLUMN more2m     HEADING ">=2M"       FORMAT B9999
COLUMN more1m     HEADING ">=1M"       FORMAT B9999
COLUMN more512k   HEADING ">=512K"     FORMAT B9999
COLUMN more256k   HEADING ">=256K"     FORMAT B9999
COLUMN more128k   HEADING ">=128K"     FORMAT B9999
COLUMN less128k   HEADING "<128K"      FORMAT B9999

SELECT ts.tablespace_name   "Tablespace", 
       more500m.nbr_segm    "more500m",
       more200m.nbr_segm    "more200m",
       more100m.nbr_segm    "more100m",
       more50m.nbr_segm     "more50m",
       more20m.nbr_segm     "more20m",
       more10m.nbr_segm     "more10m",
       more5m.nbr_segm      "more5m",
       more2m.nbr_segm      "more2m",
       more1m.nbr_segm      "more1m",
       more512k.nbr_segm    "more512k",
       more256k.nbr_segm    "more256k",
       more128k.nbr_segm    "more128k",
       less128k.nbr_segm    "less128k"
  FROM dba_tablespaces ts,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 500*1024*1024
       GROUP BY tablespace_name ) more500m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 200*1024*1024 AND bytes < 500*1024*1024
       GROUP BY tablespace_name ) more200m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 100*1024*1024 AND bytes < 200*1024*1024
       GROUP BY tablespace_name ) more100m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 50*1024*1024 AND bytes < 100*1024*1024
       GROUP BY tablespace_name ) more50m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 20*1024*1024 AND bytes < 50*1024*1024
       GROUP BY tablespace_name ) more20m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 10*1024*1024 AND bytes < 20*1024*1024
       GROUP BY tablespace_name ) more10m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 5*1024*1024 AND bytes < 10*1024*1024
       GROUP BY tablespace_name ) more5m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 2*1024*1024 AND bytes < 5*1024*1024
       GROUP BY tablespace_name ) more2m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 1024*1024 AND bytes < 2*1024*1024
       GROUP BY tablespace_name ) more1m,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 512*1024 AND bytes < 1024*1024
       GROUP BY tablespace_name ) more512k,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 256*1024 AND bytes < 512*1024
       GROUP BY tablespace_name ) more256k,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes >= 128*1024 AND bytes < 256*1024
       GROUP BY tablespace_name ) more128k,
       ( SELECT tablespace_name, count(*) nbr_segm FROM dba_free_space
          WHERE bytes < 128*1024
       GROUP BY tablespace_name ) less128k
 WHERE ts.tablespace_name = more500m.tablespace_name (+)
   AND ts.tablespace_name = more200m.tablespace_name (+)
   AND ts.tablespace_name = more100m.tablespace_name (+)
   AND ts.tablespace_name = more50m.tablespace_name (+)
   AND ts.tablespace_name = more20m.tablespace_name (+)
   AND ts.tablespace_name = more10m.tablespace_name (+)
   AND ts.tablespace_name = more5m.tablespace_name (+)
   AND ts.tablespace_name = more2m.tablespace_name (+)
   AND ts.tablespace_name = more1m.tablespace_name (+)
   AND ts.tablespace_name = more512k.tablespace_name (+)
   AND ts.tablespace_name = more256k.tablespace_name (+)
   AND ts.tablespace_name = more128k.tablespace_name (+)
   AND ts.tablespace_name = less128k.tablespace_name (+)
 ORDER BY ts.tablespace_name;

SET FEEDBACK ON