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
