Tablespace free space check

Job details

Name:	Tablespace free space check
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:	3.4
Default schedule:	45 6,11,18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above = ‘1′ & _priv_read_dba_tablespace_usage_metrics = ‘1′ & _priv_read_dba_free_space = ‘1′ & _priv_read_dba_tablespaces = ‘1′ & _priv_read_dba_tablespaces = ‘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.
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: This monitoring job is designed for dbWatch Control Center to check the level of free space available for all tablespaces in an Oracle database. It ensures that each tablespace maintains adequate free space to prevent database operations from being hindered due to space constraints.
Why: Monitoring the free space in database tablespaces is crucial as it helps in avoiding database failures or performance issues that may arise when the tablespaces run out of space. By setting warning and alarm thresholds, the job proactively alerts the DBA or system administrator to take necessary actions before reaching a critical state.
Manual checking: You can check the tablespace free space manually by issuing the following SQL commands within the Oracle database:

SELECT TABLESPACE_NAME, FILE_NAME, BYTES, MAXBYTES FROM DBA_DATA_FILES;

Features and Functionalities

The job checks tablespaces where all data files are not auto-extendable by default, although it includes parameters to adjust its behavior concerning auto-extensible files and how unallocated space is considered.
Alerts can be configured at two threshold levels—warning and alarm—based on the percentage and absolute free space (MB) available in the tablespaces.
It can exclude specific tablespaces from checks based on the provided parameters.
The job uses various Oracle dictionary views like DBA_TABLESPACES, DBA_FREE_SPACE, and other necessary views to fetch tablespaces and free space information.

Reports and Notifications

The job generates detailed reports, indicating the free space and total space for each tablespace including each space utilization by status in both warning and alarm conditions.
Additionally, the job might trigger notifications (depending on dbWatch setup) to alert the necessary personnel via email or other communication channels when tablespaces reach defined warning or alarm thresholds.

Dependencies and Setup

Various permissions need to be granted for the job to execute successfully. For instance, the SELECT privilege on DBA_DATA_FILES, DBA_TABLESPACES, etc., for the dbWatch schema user.
Installation and upgrade scripts are provided to create necessary database objects like tables and procedures required for the monitoring job’s operation.

Scheduling and Automation

By default, the job runs at a scheduled time which can be configured according to the DBA’s monitoring requirements. The default is set to specific hours, but it can be adjusted.
Being part of dbWatch Control Center, this job operates automatically once set up and scheduled, ensuring continuous monitoring without manual intervention.

Conclusion

By utilizing this dbWatch job, organizations can ensure effective space management within their Oracle database environments, enhancing overall database reliability and performance. Through proactive alerts and detailed reporting, database administrators can maintain optimal operations by addressing space-related issues before they impact the database functionality.