oracle 链式表,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

/