Script to find the list of fragmented objects in database

Script to find the list of fragmented objects in database

This below script will list the fragmented space in MB 

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",

round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",

round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"

from all_tables WHERE Owner NOT IN (

'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',

'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',

'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',

'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',

'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',

'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',

'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Script to find the list of fragmented objects in XXX_XX table

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",

round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",

round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"

from all_tables WHERE Owner = 'XXX_XX';

To defragment the tables use the below simple method

alter table <<table_name>> move + rebuild all indexes on the table indexes

Ex: alter table PIPELINE_INVENTORY move + rebuild all indexes on the table indexes

select ds.owner,

ds.segment_name,

round(ds.bytes/1024/1024,0) SIzeMBS,

round((ds.bytes-(dt.num_rows*dt.avg_row_len) )/1024/1024,0) WASTEDMBS

from dba_segments ds, dba_tables dt

where ds.owner=dt.owner

and ds.segment_name = dt.table_name

and ds.segment_type='TABLE'

and ds.segment_name='&Table_name'

group by ds.owner, ds.segment_name, round(ds.bytes/1024/1024,0) ,round((ds.bytes-(dt.num_rows*dt.avg_row_len))/1024/1024,0)

having round(bytes/1024/1024,0) >100

order by round(bytes/1024/1024,0) desc ;

Oracle Notes: 

Note: 1019716.6 - Script to Report Table Fragmentation)

Note: 186826.1 - Various Aspects of Fragmentation