frag.sql

prompt CHECKING FOR FRAGMENTED DATABASE OBJECTS:
prompt 
column Owner noprint new_value Owner_Var
column Segment_Name format a30 heading 'Object Name'
column Segment_Type format a9 heading 'Table/Indx'
column SUM(Bytes) format 999,999,999 heading 'Bytes Used'
column COUNT(*) format 999 heading 'No.'
break on Owner skip page 2
ttitle center 'Table Fragmentation Report' skip 2 -
left 'creator: ' Owner_Var skip 2
SELECT a.Owner, Segment_Name, Segment_Type, SUM(Bytes), Max_Extents, COUNT(*)
FROM DBA_EXTENTS a, DBA_TABLES b
WHERE Segment_Name = b.Table_Name 
HAVING COUNT(*) > 12
GROUP BY a.Owner, Segment_Name, Segment_Type, Max_Extents
ORDER BY a.Owner, Segment_Name, Segment_Type, Max_Extents          
/
ttitle center 'Index Fragmentation Report' skip 2 -
left 'creator: ' Owner_Var skip 2
SELECT a.Owner, Segment_Name, Segment_Type, SUM(Bytes), Max_Extents, COUNT(*)
FROM DBA_EXTENTS a, DBA_INDEXES b
WHERE Segment_Name = Index_Name 
HAVING COUNT(*) > 12
GROUP BY a.Owner, Segment_Name, Segment_Type, Max_Extents
ORDER BY a.Owner, Segment_Name, Segment_Type, Max_Extents
/