Tablespace Fragmentation (PD_PDTF)

Collection Interval

600

Y

Collection Offset

135

Y

Log

No

Y

LOGIF

(Blank)

Y

Over 10 Sec Collection Time

No

N

Avg Fragment

(AVERAGE_FRAGMENT)

Average fragment size in bytes.

The value of this field is free space when this field displays locally managed temporary tablespaces and the value of localtemp_option is Y, because fragment to the locally managed temporary tablespace is 1.

--

double

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  SUM(DBA_FREE_SPACE.BYTES) / COUNT(DBA_FREE_SPACE) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)

• For locally managed temporary tablespaces when the value of localtemp_option is Y:

  SUM(DBA_TEMP_FILES.BYTES) - (V$SORT_SEGMENT.USED_EXTENTS * AVG(V&TEMP_EXTENT_MAP.BYTES))

• For locally managed temporary tablespaces when the value of localtemp_option is N:

  SUM(V$TEMP_SPACE_HEADER.BYTES_FREE) / COUNT(V$TEMP_SPACE_HEADER) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)

Extents

(EXTENTS)

Number of extents

--

ulong

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  SUM(DBA_SEGMENTS.EXTENTS)

• For locally managed temporary tablespaces when the value of localtemp_option is N:

  SUM(V$SORT_SEGMENT.TOTAL_EXTENTS)

Fragments

(FRAGMENTS)

Number of fragments.

Always 1 for locally managed temporary tablespaces when the value of localtemp_option is Y.

--

ulong

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  COUNT(DBA_FREE_SPACE) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)

• For locally managed temporary tablespaces when the value of localtemp_option is Y:

  Remote Monitor Collector

• For locally managed temporary tablespaces when the value of localtemp_option is N:

  COUNT(V$TEMP_SPACE_HEADER) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)

High Max Extents

(HIGH_MAX_EXTENTS)

Number of segments whose PCT_MAX_EXTENTS value exceeds 90%

--

ulong

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  COUNT(DBA_SEGMENTS) where EXTENTS > MAX_EXTENTS * 0.9

• For locally managed temporary tablespaces:

  COUNT(V$SORT_SEGMENT) where EXTENT_SIZE > TOTAL_EXTENTS * 0.9

Largest Fragment

(LARGEST_FRAGMENT)

Largest fragment size in bytes

The value of this field is free space when this field displays locally managed temporary tablespaces and the value of localtemp_option is Y, because fragment to the locally managed temporary tablespace is 1.

--

double

No

All

• For dictionary managed permanent tabelspaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  MAX(DBA_FREE_SPACE.BYTES) where DBA_TABLESPACES.TABLESPACE_NAME = DBA_FREE_SPACE.TABLESPACE_NAME(+)

• For locally managed temporary tablespaces when the value of localtemp_option is Y:

  SUM(DBA_TEMP_FILES.BYTES) - (V$SORT_SEGMENT.USED_EXTENTS * AVG(V$TEMP_EXTENT_MAP.BYTES))

• For locally managed temporary tablespaces:

  MAX(V$TEMP_SPACE_HEADER.BYTES_FREE) where DBA_TEMP_FILES.FILE_ID = V$TEMP_SPACE_HEADER.FILE_ID(+)

Largest Fragment %

(LARGEST_FRAGMENT_PERCENT)

Percentage ratio of tablespace in the largest fragment

--

double

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  (MAX(DBA_FREE_SPACE.BYTES) / SUM(DBA_DATA_FILES.BYTES)) * 100

• For locally managed temporary tablespaces when the value of localtemp_option is Y:

  ((SUM(DBA_TEMP_FILES.BYTES) - (V$SORT_SEGMENT.USED_EXTENTS * AVG(V$TEMP_EXTENT_MAP.BYTES))) / SUM(DBA_TEMP_FILES_BYTES)) * 100

• For locally managed temporary tablespaces when the value of localtemp_option is N:

  (MAX(V$TEMP_SPACE_HEADER.BYTES_FREE) / SUM(DBA_TEMP_FILES.BYTES)) * 100

Next Alloc Fails

(NEXT_ALLOC_FAILS)

Indicates whether the following extent allocation failed.

For failure:

1

For success:

0

These results take effect for the following conditions:

• The tablespace is a locally managed tablespace.

• Uniform extent management is performed for the extent.

0 is returned for any other conditions.

--

ulong

No

All

• For locally managed permanent tablespaces:

  MAX(DBA_SEGMENTS.NEXT_EXTENT) > MAX(DBA_FREE_SPACE.BYTES)

Overextended

(OVEREXTENDED)

Number of segments with more than five extents

--

ulong

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  COUNT(DBA_SEGMENTS) where EXTENTS > 5

• For locally managed temporary tablespaces:

  COUNT(V$SORT_SEGMENT) where TOTAL_EXTENTS > 5

Record Time

(RECORD_TIME)

Collection termination time for the performance data stored in the record

--

time_t

No

All

Remote Monitor Collector

Record Type

(INPUT_RECORD_TYPE)

Record name (always PDTF)

--

string(4)

No

All

Remote Monitor Collector

Segments

(SEGMENTS)

Number of segments

Always 1 for locally managed temporary tablespaces when the value of localtemp_option is Y.

--

ulong

No

All

• For dictionary managed permanent tablespaces, locally managed permanent tablespaces, or dictionary managed temporary tablespaces:

  COUNT(DBA_SEGMENTS)

• For locally managed temporary tablespaces when the value of localtemp_option is Y:

  Remote Monitor Collector

• For locally managed temporary tablespaces when the value of localtemp_option is N:

  COUNT(V$SORT_SEGMENT)

Start Time

(START_TIME)

Collection start time for the performance data stored in the record

--

time_t

No

All

Remote Monitor Collector

Tablespace Name

(TABLESPACE_NAME)

Tablespace name

--

string(30)

No

All

DBA_TABLESPACES.TABLESPACE_NAME

VA DeviceID

(VADEVICEID)

Device ID of virtual agent.

--

string(256)

No

All

Remote Monitor Collector