Script:列出数据库中5%以上链式行的表

以下脚本用于列出数据库中chained/migrated rows达到5%的表，注意查询结果来源于统计信息，如果数据库长期没有gather_stats则结果不真实:

REM List Tables with > 5 % chained rows and > 500 total rows 

  SELECT owner,
         table_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_all_tables
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/

REM List Table Partitions with > 5 % chained rows and > 500 total rows 

  SELECT table_owner,
         table_name,
         partition_name,
         pct_free,
         ROUND (100 * chain_cnt / num_rows, 0) chain_pct
    FROM sys.dba_tab_partitions
   WHERE ROUND (100 * chain_cnt / num_rows, 0) > 5
         AND table_owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND num_rows IS NOT NULL
         AND num_rows > 500
ORDER BY 1, 2
/