1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
prompt Fragmentation report - If number of extents is approaching Maxextents,
prompt it is time to defragment the table.
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(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
/ |
Partager