Segment size status for old style tablespaces


Job details

Name: Segment size status for old style tablespaces
Platform: Oracle
Category: Capacity
Description: Checks segment storage definitions in tablespaces where extent management is not set to local.
Long description: Task checks segment storage definitions in tablespaces where extent management is not set to local.
Version: 1.8
Default schedule: 40 6,17 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘10%’ version like ‘8%’ version like ‘9%’)]

Parameters

Name Default value Description
segm def next/total 40 Pct threshold for storage, value next_extent
segm def min size 200 Only segments with total size greater than the value of the dbWatch parameter segm def min size are checked
segm def ext/max 10 Threshold for storage parameter values for a segment. If the difference between extents and max extents
segm def ext# 2000 Threshold for max-extents value for a segment.

Job Summary

SELECT segment_type, owner, segment_name, tablespace_name, bytes/1024 AS size_kb, next_extent/1024 AS next_extent_kb
FROM dba_segments
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE CONTENTS = 'PERMANENT' AND extent_management = 'DICTIONARY')
ORDER BY next_extent DESC;

Task Details

Implementation

Reporting and Output

Parameters Utilized

Output Tables

Table Name Description
Tablespace storage definition Lists each tablespace and segment storage details where extent management is not localized.
History table Includes historical segment storage data that are critical for analyzing trends and changes over time.

These tables aid in proactive database space management by alerting on potential configuration issues before they escalate into more significant problems.