Tablespace free space

Job details

Name:	Tablespace free space
Platform:	Oracle
Category:	Capacity
Description:	Checks level of free space for all tablespaces.
Long description:	Task checks the level of free space for all tablespaces.
Version:	2.4
Default schedule:	45 6,11,18 * *
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
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.
ignore AUTOEXTENSIBLE	NO	If “YES“, the procedure does not check tablespaces with AUTOEXTENSIBLE data-files (AUTOEXTENSIBLE=”YES“). Possible values are “YES“ and “NO”.
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.
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 purpose of this job is to monitor the free space available in Oracle tablespaces for instances running versions 8, 9, or 10. It aims to identify tablespaces with critical or warning levels of free space, potentially preventing issues related to space exhaustion.
Why: This job is important as it helps maintain the health and efficiency of the database by ensuring that tablespaces have sufficient free space. Low space can lead to performance degradation and may halt database operations if not managed timely.
Manual checking: You can check tablespaces manually by issuing the following SQL command in Oracle:

SELECT TABLESPACE_NAME, FILE_ID, BYTES, MAXBYTES FROM DBA_DATA_FILES;

Detailed Description

The task specifically targets Oracle database versions 8, 9, and 10, focusing on instances where the database engine is active.
It includes comprehensive checks for tablespace free space based on various thresholds, warning if space falls below set percentages or MB thresholds. Alarm conditions are similarly raised if more critical thresholds are crossed.
Tablespace exclusion allows skipping specific tablespaces from the space check, useful when monitoring is unnecessary or misleading.

Space Measurements

Percentage and absolute MB thresholds for warnings and alarms can be configured to tailor sensitivity of alerts.
The consideration of AUTOEXTENSIBLE tablespaces can also be toggled. This affects whether the job takes into account the potential for tablespaces to grow automatically and possibly mitigate space constraints independently.

Operational Settings

The default task schedule is set to execute at 6:45, 11:45, and 18:45 every day, ensuring timely and regular checks.

Dependencies and Reports

This task depends internally on a required table ts_free_space_tab and self-dependency under certain checks for execution continuity.
The task can generate detailed reports with information about tablespaces that are explicitly identified under warning or alarm conditions, including data such as tablespaces names, total size, free space (MB & percent), and their statuses (online, offline, etc.).

Implementation Details

A specific SQL script is used to create necessary objects and procedures. It includes error handling to manage any issues during its execution.
The procedures utilized gather data from the Oracle dictionary views like DBA_TABLESPACES, DBA_FREE_SPACE, and DBA_DATA_FILES, ensuring comprehensive space coverage.

Tables and Presentations

Tablespace Name	Total Size (MB)	Space Free (MB)	Pct Free	Space Free MAX	Pct Free MAX	Max Size	Status
-------------------	-------------------	-------------------	-----------	------------------	----------------	------------	---------
Example Data	1024	300	29.3%	500	48.8%	1024	ONLINE

Additional visual presentations in reports provide clear insights on space utilization and help in swift decision making during database capacity planning and management activities.