SQL Script

Table Fragmentation Script

Updated : 13-Sep-2002
Version : 1.0

Description

This script lists details how chained or migrated rows there are within a table. It may help you determine if a table needs to be rebuilt. In order for this script to be effective, you must analyze your tables regularly.

Parameters

None.

SQL Source

CLEAR
SET HEAD ON
SET VERIFY OFF
set pages 100
set lines 79

PROMPT 
PROMPT Table Fragmentation Report
PROMPT

col owner form a12
col table_name form a20
col empty_blocks form 999,999 heading "Empty Blks"
col blocks form 999,999 heading "Blks"
col pct form 99

select owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where chain_cnt > 0
and owner not in ('SYS','SYSTEM')
/

exit