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
/

© 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Script:列出没有主键或唯一索引的表
2. Script:收集数据库安全风险评估信息
3. Script:列出数据库中子表上没有对应索引的外键
4. Script:收集RAC性能诊断信息
5. Script:Tablespace Report
6. Script:Datafile Report
7. Script:List Buffer Cache Details
8. Script:partition table into rowid extent chunks
9. Script: Computing Table Size
10. Script:Generate A DDL Script For A Table