Fragmentation in Database

[somanivivek]

How do I find the amount of fragmentation in database.
I got hold of a query which says that all objects with extent_id>3 are deemed as fragmented. Is is true?? Any other method to find fragmentation??

 

[rcurva]

You might be referring to this kind of SQL;

SELECT SEGMENT_NAME ,
SEGMENT_TYPE ,
TABLESPACE_NAME ,
COUNT( EXTENT_ID ) EXTENTS_#,
SUM( BYTES ) TOT_BYTES,
SUM( BLOCKS ) TOT_BLOCKS
FROM ALL_EXTENTS
GROUP BY SEGMENT_NAME , SEGMENT_TYPE , TABLESPACE_NAME
having count( extent_id ) > 3;

This is a common misconception, the real advantage is gained by using larger extent sizes and not having small number of extents.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War