Hitachi

JP1 Version 12 JP1/Performance Management - Remote Monitor for Oracle Description, User's Guide and Reference

Tablespace (PD_PDTS)

Organization of this page

Function

The Tablespace (PD_PDTS) record stores performance data indicating the status (at a specific point in time) of tablespaces in a database. PFM - RM for Oracle creates one record for each tablespace in a database. This is a multi-instance record.

To monitor the tablespace size, the size of the currently allocated space can be monitored. However, if automatic expansion is enabled, the size of the unallocated space can also be monitored. The following figure shows the relationship between the fields associated with tablespace sizes.

Figure 5‒3: Relationship between the fields associated with tablespace sizes

[Figure]

If the size of automatic expansion is unlimited, the Max Extend Mbytes field displays the value based on the theoretical maximum size that can be set for Oracle Database. The values of the other fields are calculated based on the value of the Max Extend Mbytes field.

The values set for localtemp_option and undospace_option in the instance information affect only values of the Used Mbytes field and Free Mbytes field. The following figures show effects of the values of localtemp_option and undospace_option.

Figure 5‒4: Effects of the value of localtemp_option

[Figure]
Figure 5‒5: Effects of the value of undospace_option

[Figure]

To Page Top

Default and changeable values

Item

Default value

Changeable

Collection Interval

3600

Y

Collection Offset

30

Y

Log

No

Y

LOGIF

(Blank)

Y

Over 10 Sec Collection Time

No

N

To Page Top

ODBC key fields

PD_PDTS_TABLESPACE_NAME

To Page Top

Lifetime

From the creation to the deletion of a tablespace

To Page Top

Record size

To Page Top

Fields

PFM - View name

(PFM - Manager name)

Description

Summary

Format

Delta

Supported version

Data source

Auto Extensible

(AUTO_EXTENSIBLE)

Indicates whether automatic expansion of the tablespace is enabled when the monitoring target is Oracle Database 11g R2 or later.

YES is set if automatic expansion is enabled. NO is set if automatic expansion is disabled. YES is set if automatic expansion is enabled for at least one of the data files which comprise the tablespace.

A blank is set if the monitoring target is Oracle Database 11g R1 or earlier.

--

string(3)

No

All

DBA_DATA_FILES.AUTOEXTENSIBLE

Blocks

(BLOCKS)

Size of tablespace in Oracle blocks

--

ulong

No

All

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

    SUM(DBA_DATA_FILES.BLOCKS)

  • For locally managed temporary tablespaces:

    SUM(DBA_TEMP_FILES.BLOCKS)

Data Files

(DATAFILES)

Number of data files in use by the tablespace

--

ulong

No

All

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

    COUNT(DBA_DATA_FILES)

  • For locally managed temporary tablespaces:

    COUNT(DBA_TEMP_FILES)

Extensible Mbytes

(EXTENSIBLE_BYTES)

Size remaining in megabytes for automatic expansion when the monitoring target is Oracle Database 11g R2 or later.

0 is set if automatic expansion is disabled, the maximum size has already been reached, or the monitoring target is Oracle Database 11g R1 or earlier.

--

double

No

All

MAX_BYTES - BYTES

Extensible Mbytes %

(PERCENT_EXTENSIBLE_BYTES)

Percentage of space remaining for automatic expansion if the monitoring target is Oracle Database 11g R2 or later.

0 is set if automatic expansion is disabled, the maximum size has already been reached, or the monitoring target is Oracle Database 11g R1 or earlier.

--

double

No

All

(EXTENSIBLE_BYTES / MAX_BYTES) * 100

Extents

(EXTENTS)

Number of extents

--

ulong

No

All

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

    SUM(DBA_SEGMENTS.EXTENTS)

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

    SUM(DBA_TEMP_FILES.BYTES / V$TEMP_EXTENT_MAP.BYTES)

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

    SUM(V$SORT_SEGMENT.TOTAL_EXTENTS)

Free %

(PERCENT_FREE)

Percentage ratio of free space

--

double

No

All

  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, dictionary managed temporary tablespaces, or the UNDO tablespaces when the value of undospace_option is N:

    (SUM(DBA_FREE_SPACE.BYTES) / 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))) / DBA_TEMP_FILES.BYTES) * 100

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

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

  • For the UNDO tablespaces when the value of undospace_option is Y:

    ((SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / DBA_DATA_FILES.BYTES) * 100

Free Extents

(FREE_EXTENTS)

Number of free extents.

--

ulong

No

All

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

    COUNT(DBA_FREE_SPACE)

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

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

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

    COUNT(V$TEMP_SPACE_HEADER)

Free Mbytes

(FREE_BYTES)

Size of free space in megabytes.

--

double

No

All

  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, dictionary managed temporary tablespaces, or the UNDO tablespaces when the value of undospace_option is N:

    SUM(DBA_FREE_SPACE.BYTES) / (1024 * 1024)

  • 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))) / (1024 * 1024)

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

    SUM(V$TEMP_SPACE_HEADER.BYTES_FREE) / (1024 * 1024)

  • For the UNDO tablespaces when the value of undospace_option is Y:

    (SUM(DBA_FREE_SPACE.BYTES) + SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / (1024 x 1024)

Increase %

(PCT_INCREASE)

Default rate of increase in the extent size

--

short

No

All

DBA_TABLESPACES.PCT_INCREASE

Initial Extent

(INITIAL_EXTENT)

Default size of the initial extent

--

double

No

All

DBA_TABLESPACES.INITIAL_EXTENT

Max Extend Free %

(MAX_PERCENT_FREE)

Percentage of the unused size in relation to the maximum automatically expandable size if the monitoring target is Oracle Database 11g R2 or later.

The value is the same as the Free % field when automatic expansion is disabled or the maximum size has already been reached. The value is 0 when the monitoring target is Oracle Database 11g R1 or earlier.

--

double

No

All

(MAX_FREE_BYTES / MAX_BYTES) * 100

Max Extend Free Mbytes

(MAX_FREE_BYTES)

Size unused when automatic expansion is enabled and the monitoring target is Oracle Database 11g R2 or later. This size is the total of the unused size and the automatically expandable size in megabytes. The value is the same as the Free Mbytes field when automatic expansion is disabled or the maximum size has already been reached.

The value is 0 when the monitoring target is Oracle Database 11g R1 or earlier.

--

double

No

All

MAX_BYTES - USED_BYTES

Max Extend Mbytes

(MAX_BYTES)

Maximum automatic expandable size (in megabytes) when the monitoring target is Oracle Database 11g R2 or later.

The value is the same as the Mbytes field when automatic expansion is disabled or the maximum size has already been reached.

The value is 0 when the monitoring target is Oracle Database 11g R1 or earlier.

--

double

No

All

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

    SUM(DBA_DATA_FILES.MAXBYTES) / (1024 * 1024)

  • For locally managed temporary tablespaces:

    SUM(DBA_TEMP_FILES.MAXBYTES) / (1024 * 1024)

Max Extents

(MAX_EXTENTS)

Default maximum number of extents

--

ulong

No

All

DBA_TABLESPACES.MAX_EXTENTS

Mbytes

(BYTES)

Size of the tablespace in megabytes

--

double

No

All

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

    SUM(DBA_DATA_FILES.BYTES) / (1024 * 1024)

  • For locally managed temporary tablespaces:

    SUM(DBA_TEMP_FILES.BYTES) / (1024 * 1024)

Min Extents

(MIN_EXTENTS)

Default minimum number of extents

--

long

No

All

DBA_TABLESPACES.MIN EXTENTS

Next Extent

(NEXT_EXTENT)

Default size of the incremental extent

--

double

No

All

DBA_TABLESPACES.NEXT_EXTENT

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 PDTS)

--

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

Status

(STATUS)

Tablespace status. Valid values are INVALID (tablespace was deleted), OFFLINE, and ONLINE.

--

string(9)

No

All

DBA_TABLESPACES STATUS

Tablespace Name

(TABLESPACE_NAME)

Tablespace name

--

string(30)

No

All

DBA_TABLESPACES.TABLESPACE_NAME

Used Mbytes

(USED_BYTES)

Size of used area in megabytes.

--

double

No

All

  • For dictionary managed permanent tablespaces, locally managed permanent tablespaces, dictionary managed temporary tablespaces, or the UNDO tablespaces when the value of undospace_option is N:

    (SUM(DBA_DATA_FILES.BYTES) - SUM(DBA_FREE_SPACE.BYTES)) / (1024 *1024)

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

    (V$SORT_SEGMENT.USED_EXTENTS * AVG(V$TEMP_EXTENT_MAP.BYTES)) / (1024 * 1024)

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

    (SUM(DBA_TEMP_FILES.BYTES) - SUM(V$TEMP_SPACE_HEADER.BYTES_FREE)) / (1024 *1024)

  • For the UNDO tablespaces when the value of undospace_option is Y:

    (DBA_DATA_FILES.BYTES - SUM(DBA_FREE_SPACE.BYTES) - SUM(DBA_UNDO_EXTENTS.BYTES) WHERE STATUS='EXPIRED') / (1024 * 1024)

VA DeviceID

(VADEVICEID)

Device ID of virtual agent.

--

string(256)

No

All

Remote Monitor Collector

To Page Top

Copyright (C) 2019, 2020, Hitachi, Ltd.

Copyright (C) 2019, 2020, Hitachi Solutions, Ltd.