查询Schema中浪费空间超过25%的表

table_fragmentation.sql

-- 各列的说明：
-- WASTE_PER：已分配空间中水线以下的空闲空间(即浪费空间)的百分比。
-- TABLE_KB：该表目前已经分配的所有空间的大小，以k为单位。
-- NUM_ROWS：在在表中数据的行数
-- BLOCKS：该表目前已经分配的数据块的块数，包含水线以上的部分
-- EMPTY_BLOCKS：已分配空间中水线以上的空闲空间
-- HIGHWATER_MARK：目前的水线
-- AVG_USED_BLOCKS：理想情况下(没有行迁移)，该表数据应该占用的数据块的个数
-- CHAIN_PER：发生行迁移现象的行占总行的比率
-- EXTENTS：该表目前已经分配的extent数
-- MAX_EXTENTS：该表可以分配的最大extent的个数
-- ALLO_EXTENT_PER：目前已分配的extent的个数占可以分配最大extent的比率
-- CAN_EXTEND_SPACE：是否可以分配下一个extent
--N EXT_EXTENT：下一个extent的大小
-- MAX_FREE_SPACE：表的已分配空间中最大的空闲空间

-- 此脚本可以查询出浪费空间的表(浪费超过25%)，而且还计算出其它信息(使用时根据
-- 具体情况修改owner条件：

SELECT   owner, segment_name table_name, segment_type,
         GREATEST (ROUND (  100
                          * (  NVL (hwm - avg_used_blocks, 0)
                             / GREATEST (NVL (hwm, 1), 1)
                            ),
                          2
                         ),
                   0
                  ) waste_per,
         ROUND (BYTES / 1024, 2) table_kb, num_rows, blocks, empty_blocks,
         hwm highwater_mark, avg_used_blocks, chain_per, extents, max_extents,
         allo_extent_per,
         DECODE (GREATEST (max_free_space - next_extent, 0),
                 0, 'N',
                 'Y'
                ) can_extend_space,
         next_extent, max_free_space, o_tablespace_name tablespace_name
    FROM (SELECT a.owner owner, a.segment_name, a.segment_type, a.BYTES,
                 b.num_rows, a.blocks blocks, b.empty_blocks empty_blocks,
                 a.blocks - b.empty_blocks - 1 hwm,
                   DECODE (ROUND (  (  b.avg_row_len
                                     * num_rows
                                     * (1 + (pct_free / 100))
                                    )
                                  / c.BLOCKSIZE,
                                  0
                                 ),
                           0, 1,
                           ROUND (  (  b.avg_row_len
                                     * num_rows
                                     * (1 + (pct_free / 100))
                                    )
                                  / c.BLOCKSIZE,
                                  0
                                 )
                          )
                 + 2 avg_used_blocks,
                 ROUND (  100
                        * (  NVL (b.chain_cnt, 0)
                           / GREATEST (NVL (b.num_rows, 1), 1)
                          ),
                        2
                       ) chain_per,
                 ROUND (100 * (a.extents / a.max_extents), 2) allo_extent_per,
                 a.extents extents, a.max_extents max_extents,
                 b.next_extent next_extent,
                 b.tablespace_name o_tablespace_name
            FROM SYS.dba_segments a, SYS.dba_tables b, SYS.ts$ c
           WHERE a.owner = b.owner
             AND segment_name = table_name
             AND segment_type = 'TABLE'
             AND b.tablespace_name = c.NAME
          UNION ALL
          SELECT a.owner owner, segment_name || '.' || b.partition_name,
                 segment_type, BYTES, b.num_rows, a.blocks blocks,
                 b.empty_blocks empty_blocks,
                 a.blocks - b.empty_blocks - 1 hwm,
                   DECODE (ROUND (  (  b.avg_row_len
                                     * b.num_rows
                                     * (1 + (b.pct_free / 100))
                                    )
                                  / c.BLOCKSIZE,
                                  0
                                 ),
                           0, 1,
                           ROUND (  (  b.avg_row_len
                                     * b.num_rows
                                     * (1 + (b.pct_free / 100))
                                    )
                                  / c.BLOCKSIZE,
                                  0
                                 )
                          )
                 + 2 avg_used_blocks,
                 ROUND (  100
                        * (  NVL (b.chain_cnt, 0)
                           / GREATEST (NVL (b.num_rows, 1), 1)
                          ),
                        2
                       ) chain_per,
                 ROUND (100 * (a.extents / a.max_extents), 2) allo_extent_per,
                 a.extents extents, a.max_extents max_extents, b.next_extent,
                 b.tablespace_name o_tablespace_name
            FROM SYS.dba_segments a,
                 SYS.dba_tab_partitions b,
                 SYS.ts$ c,
                 SYS.dba_tables d
           WHERE a.owner = b.table_owner
             AND segment_name = b.table_name
             AND segment_type = 'TABLE PARTITION'
             AND b.tablespace_name = c.NAME
             AND d.owner = b.table_owner
             AND d.table_name = b.table_name
             AND a.partition_name = b.partition_name),
         (SELECT   tablespace_name f_tablespace_name,
                   MAX (BYTES) max_free_space
              FROM SYS.dba_free_space
          GROUP BY tablespace_name)
   WHERE f_tablespace_name = o_tablespace_name
     AND GREATEST (ROUND (  100
                          * (  NVL (hwm - avg_used_blocks, 0)
                             / GREATEST (NVL (hwm, 1), 1)
                            ),
                          2
                         ),
                   0
                  ) > 25
     AND owner = 'Schema Name'
     AND blocks > 128   
ORDER BY 4 DESC, 5 DESC;