Autoextensible data files
Job details
| Name: | Autoextensible data files |
| Platform: | Oracle |
| Category: | Capacity |
| Description: | Checks all tablespaces for total disk space usage (autoextensible data files). |
| Long description: | Task checks all tablespaces for total disk space usage (autoextensible data files). |
| Version: | 3.01 |
| Default schedule: | 55 6,11,18 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_dba_data_files = ‘1′] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning threshold (percent) | 10 | Warning threshold for the minimum value of the space (in percentage) which can be allocated to a data file(s) per tablespace before reaching the maximum size of the data file(s). If the amount of space (which can be allocated) falls below this parameter value, and at the same time the amount of space in MB falls below the “warning threshold (MB)” parameter value, the alert will return a WARNING. |
| exclude tablespaces | Name of the tablespace(s) which will be excluded from being checked (separated by comma). You can use % (percent sign) to represent wild card characters. | |
| warning threshold (MB) | 5000 | Minimum of the space (in MB) which can be allocated to a data file(s) per tablespace. If the amount of space (which can be allocated) falls below this parameter value, and at the same time the amount of space in percent falls below the “warning threshold (percent)” parameter value, the alert will return a WARNING. |
| alarm threshold (percent) | 5 | Alarm threshold for the minimum value of the space (in percentage) which can be allocated to a data file(s) per tablespace before reaching the maximum size of the data file(s). If the amount of space (which can be allocated) falls below this parameter value, and at the same time the amount of space in MB falls below the “alarm threshold (MB)” parameter value, the alert will return an ALARM. |
| alarm threshold (MB) | 2000 | Minimum of the space (in MB) which can be allocated to a data file(s) per tablespace. If the amount of space (which can be allocated) falls below this parameter value, and at the same time the amount of space in percent falls below the “alarm threshold (percent)” parameter value, the alert will return an ALARM. |
| ignore if free space (in MB) | 10000 | Ignore WARNING and ALARM if there is free space inside the data file(s). |
Job Summary
- Purpose: The purpose of this job is to monitor and manage autoextensible data files within Oracle databases by analyzing space usage and growth potential.
- Why: Monitoring autoextensible data files is crucial to prevent potential space allocation issues, ensuring that tablespaces can grow as needed without reaching maximum size limits unexpectedly. This helps in maintaining database performance and availability.
- Manual checking: You can check the status manually by executing the following SQL commands:
select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKNOWN')) "Tablespace name",
b.file_count "File count", round(mbytes_alloc) "Total size",
round(mbytes_alloc-nvl(mbytes_free,0),1) "Space used",
round(nvl(mbytes_free,0),1) "Space free",
round(((mbytes_alloc-nvl(mbytes_free,0))/mbytes_alloc)*100,1) "Pct used",
round(nvl(largest,0),1) "Largest free ext.",
round(nvl(mbytes_max,mbytes_alloc)) "Max size",
round(decode( mbytes_max, 0, 0, (mbytes_alloc/mbytes_max)*100),1) "Pct max used",
c.status "Status"
from ...Implementation Details
The job uses a stored procedure named “autoext_db_files” which executes several SQL queries to fetch data about tablespaces with attributes such as total size, space used, space free, percentage used, maximum size, and percentage of maximum used. The procedure also checks if tablespaces are approaching configured threshold limits and generates appropriate alerts (warning or alarm) based on conditions set for space usage percentages and absolute free space values.
Alerts and Notifications
- Warning: Triggered if the available space or the percent of maximum allocated space falls below the specified warning thresholds (either in MB or percentage).
- Alarm: Triggered under more severe conditions comparable to the warning, but with lower thresholds, indicating more immediate attention may be required.
Parameters Description
| Parameter | Description |
|---|---|
| Warning threshold (percent) | Minimum percentage of space to allocate before a warning is triggered |
| Warning threshold (MB) | Minimum space (in MB) to allocate before a warning is triggered |
| Alarm threshold (percent) | Minimum percentage of space to allocate before an alarm is triggered |
| Alarm threshold (MB) | Minimum space (in MB) to allocate before an alarm is triggered |
| Exclude tablespaces | Names of tablespaces that should be excluded from checks |
| Ignore if free space (in MB) | Threshold of free space to ignore the warning and alarm conditions |
Compatibility and Execution Schedule
The job is tailored for Oracle database instances and it runs on a default schedule as configured in its settings. The job ensures compatibility by performing checks that it runs only on Oracle instances with the necessary privileges to read DBA data files.
Implementing monitoring for autoextensible data files aids in proactive database management, reducing the risks of data growth-related disruptions, and allowing DBAs to make informed decisions about capacity planning and optimization.