OBJECTS WITH MORE THAN 50% OF MAXEXTENTS

 

Segment Fragmentation

OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:

Owner - Owner of the object

Tablespace Name - Name of the tablespace

Segment Name - Name of the segment

Segment Type - Type of segment

Size - Size of the object (bytes)

Extents - Current number of extents

Max Extents - Maximum extents for the segment

Percentage - Percentage of extents in use

As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.

To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7

Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017

Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.

select  OWNER,
 TABLESPACE_NAME,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 BYTES,
 EXTENTS,
 MAX_EXTENTS,
 (EXTENTS/MAX_EXTENTS)*100 percentage
from  dba_segments
where  SEGMENT_TYPE in ('TABLE','INDEX')
and  EXTENTS > MAX_EXTENTS/2
order  by (EXTENTS/MAX_EXTENTS) desc