Temporary tablespace free space check

Job details

Name:	Temporary tablespace free space check
Platform:	Oracle
Category:	Capacity
Description:	Checks level of free space for temporary tablespaces.
Long description:	Task checks the level of free space for temporary tablespaces.
Version:	0.5
Default schedule:	45 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_dba_tablespace_usage_metrics = ‘1′ & _priv_read_dba_free_space = ‘1′ & _priv_read_dba_tablespaces = ‘1′ & _priv_read_dba_temp_files = ‘1′ & _priv_read_dba_temp_free_space=‘1′]

Parameters

Name	Default value	Description
warning threshold (percent)	10	Warning threshold for the minimum value of the free space in the tablespace (in percentage). If the amount of free space falls below this parameter value, and at the same time the amount of free space in MB falls below the “warning threshold (MB)” parameter value, the check will return a WARNING.
exclude tablespaces		Name of the tablespace(s) which should be excluded from free-space checking (separated by comma).
warning threshold (MB)	5000	Minimum of the free space (in MB) for the tablespace. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “warning threshold (percent)” parameter value, the check will return a WARNING.
alarm threshold (percent)	5	Alarm threshold for the minimum value of free space in the tablespace (in percentage). If the amount of free space falls below this parameter value, and at the same time the amount of free space in MB falls below the “alarm threshold free space MB” parameter value, the check will return an ALARM.
alarm threshold (MB)	2000	Minimum of free space (in MB) for the tablespace. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “alarm threshold (percent)” parameter value, the check will return an ALARM.
ignore AUTOEXTENSIBLE	NO	If “YES“, the procedure does not check tablespaces with AUTOEXTENSIBLE data-files (AUTOEXTENSIBLE=”YES“). Possible values are “YES“ and “NO”.
include unallocated free space	NO	If set to “YES“, the procedure will compute the free space in each tablespace based on available space in the data file plus all unallocated space this file could autoextend to (maxsize – current size).

Job Summary

Purpose: The main purpose of this job is to monitor and ensure the appropriate levels of free space within Oracle temporary tablespaces, safeguarding against potential space-related issues that might affect database performance and operations.
Why: Monitoring the free space in temporary tablespaces is crucial:
- It prevents scenarios where lack of space could lead to failed transactions or performance degradation, which is particularly significant in environments with high levels of data manipulation and temporary data storage (e.g., sort operations, large queries).
- By setting thresholds, the system can proactively alert administrators about potential space shortages, allowing for timely intervention before it impacts the database functionality.
Manual checking: You can manually check the temporary tablespaces’ free space in Oracle by running the following query:

SELECT tablespace_name, file_name, BYTES, MAXBYTES FROM dba_temp_files;

Monitoring Metrics

Metric	Description
Category	Capacity-related checks to monitor space utilization.
Object	tmp_free_space – Focuses on free space within temporary tablespaces.
Default Schedule	Every 45 minutes (indicated by the cron expression “45 * * *”).

Thresholds and Alerts

This job is configured to issue alerts based on predefined thresholds of free space both in percentage and megabytes (MB).
When the free space falls below these thresholds, alerts are generated to notify the database administration team. The system compares both percentage and absolute values (MB) to decide the alert severity (Warning or Alarm).

Configuration and Compatibility

The job is compatible solely with Oracle database instances, leveraging specific Oracle views such as DBA_TEMP_FILES and DBA_FREE_SPACE.
Hidden properties and compatibility:
- The monitoring job relies on privileges to access necessary database views. It requires permissions to select from several DBA views pertinent to tablespaces and space metrics.

Report Details

Report Title: Free Temporary Space Check
Description and Content: The report provides a detailed presentation of free space status in temporary tablespaces.
Report Sections:
- “Warning/Alarm details” table: Lists tablespaces nearing or below the set thresholds.
- Additional visual charts and tables provide an overview of current space usage versus maximum available size, thus helping to pinpoint instances that might soon require intervention.

This job’s implementation assures that the temporary tablespaces maintain enough free space efficiently, by providing both on-the-spot checks and historical data trends for capacity planning and operational reliability. These factors are quintessential for maintaining a database environment that is both stable and scalable.