以下脚本用于列出数据库中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 /
第一个查的是sys.dba_tables吧
第一段复制错了,因该是sys.dba_all_tables,已修正。
这个应该要先 analyze 不然看不到chain_cnt 吧
用包分析好象不可以的