Tablespace free space
Job details
| Name: | Tablespace free space |
| Platform: | Oracle |
| Category: | Capacity |
| Description: | Checks the maximum number of processes. |
| Long description: | |
| Version: | 1.6 |
| Default schedule: | 2h |
| Requires engine install: | No |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning_free_mb | 5000 | MB space left in tablespace when issuing a warning |
| alarm_free_mb | 2000 | MB space left in tablespace when issuing an alarm |
| warning_free_pct | 10 | Percent space left in tablespace when issuing a warning |
| alarm_free_pct | 5 | Percent space left in tablespace when issuing an alarm |
| include_unallocated_free_space | NO | Include unallocated free space, this will add possible autoextensible space to free space and requires you to have monitoring of actual diskspace. Values are YES or NO |
| exclude_tablespace | Tablespaces to exclude, comma separated | |
| query_timeout | 60 | Specifies the maximum duration in seconds that the SELECT query against the dba_free_space and dba_data_files views can run. If the query takes longer than this limit, it will be terminated to conserve resources. |
Job Summary
- Purpose: The job “Oracle alert Tablespace free space noschema” is designed to monitor the availability of free space in the tablespaces of an Oracle database.
- Why: This monitoring is critical for preventing space-related issues that could lead to database performance degradation or unavailability. It enables proactive management of database capacity, ensuring there is enough space for operations and growth without unexpected disruptions.
- Manual Checking: To manually check tablespaces free space, you can use the following SQL command:
SELECT nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKNOWN')) t_name, round(nvl(mbytes_free,0),1) free_MB, 100 - round(((mbytes_alloc-nvl(mbytes_free,0))/mbytes_alloc)*100,1) pct_free, round(nvl(mbytes_max,mbytes_alloc)) - round(mbytes_alloc-nvl(mbytes_free,0),1) free_MB_MAX, round(((round(nvl(mbytes_max,mbytes_alloc)) - round(mbytes_alloc-nvl(mbytes_free,0),1))/mbytes_max)*100,1) pct_free_MAX FROM ( SELECT sum(bytes)/1048576 mbytes_free, max(bytes)/1048576 largest,tablespace_name FROM sys.dba_free_space GROUP BY tablespace_name ) a, ( SELECT sum(bytes)/1048576 mbytes_alloc, sum(decode(maxbytes,0,bytes ,maxbytes))/1048576 mbytes_max,tablespace_name FROM sys.dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name (+) = b.tablespace_name ORDER BY 3 ASC;Details and Logic
The job performs the following comprehensive checks:
- It initially checks if the instance is open. If the instance is not open, it registers a status that free space check is not possible.
- The job performs a dynamic SQL query that computes both the actual free space and maximum possible free space (accounting for auto-extend options if enabled).
- Conditions are evaluated based on user-defined thresholds:
- If actual free space or percentage free is below ‘alarm’ thresholds, a critical alert is generated.
Reporting
- The job results are reported in a tabular format showing:
- Tablespace Name.
Report generated via dbWatch uses SQL and JavaScript for dynamic response based on the live database data.