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

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

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.