Is this script the best one to find fragmentation in segments:
SELECT segment_name table_name , COUNT(*) extents
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) )
GROUP BY segment_name)
An ounce of prevention is worth a pound of cure
well number of extents is not really fragmentation
plus that script is wrong, you dont need to count to get number of extents, there is a column called extents in dba_segmentS!
The script above won't return any useful data. Check ths scripts available in the script section of this website.
Actually Farrokhp has taken one of the script that's given in the URL given by
Sanjay_G. The No. of extents allocated for a table helps a little to find out
the fragmentation in a segment. As far as fragmentation in a segment is concerned,
it happens due to large no. of delete/update statements for the rows existing in a segment.
The no. of extents for a segment helps in the following way:
Assume that initially the segment consisted around 10,000 rows which was stored in
say 10 extents. Then due to heavy deletion and updation activity the no. of records
had come down to 100 rows. In this case, the 100 rows can be available in any of
the 10 extents. It need not be contigous. For finding out whether this had happened,
you can use the ROWID psuedocolumn and get the information from DBA_EXTENTS.
But this is automatically de-fragmented when inserts happen for the next time. If you find that
a segment is heavily fragmented then you can export and import the table's records.
This is known as "Re-Organization" of tables.
I am not sure whether this script is from dbasupport.com but it is wrong. Note that he is selecting from dba_segments (not dba_extents) and grouping by segment_name.. If he wants to know number of segments one could simply select "extents" from dba_segments..
Click Here to Expand Forum to Full Width