Oracle DBA Lesson 97 Part 1 — Set a threshold with time to respond

A useful tablespace alert reaches someone while there is still enough time to protect application service. Choose warning and critical levels from usable capacity, recent growth and the time needed to investigate and correct the condition. A percentage becomes useful when you know its denominator and what will consume the remaining space.

Oracle DBA Lesson 97 Part 1 — Set a threshold with time to respond

Turn remaining capacity into response time

The calculation below applies to an online, locally managed permanent tablespace. Temporary and undo usage have additional behavior and require their own interpretation.

Suppose a fixed-capacity tablespace has 10 GB of usable capacity, 8 GB consumed and a recent net growth rate of 0.5 GB/hour. GB means decimal gigabytes, and the capacity remains unchanged during the estimate.

Remaining capacity = 10 GB − 8 GB = 2 GB
Estimated time to capacity = 2 GB / 0.5 GB per hour = 4 hours

This is a planning estimate under the stated growth assumption. A burst, a large allocation, competing storage consumption or a changed file limit can shorten it. An allocation may fail before an aggregate estimate reaches zero if the necessary extent cannot be allocated in an eligible file. Recalculate from current evidence and keep margin.

Compare the estimate with the entire response path: metric evaluation, notification delivery, acknowledgement, diagnosis, access to the necessary resources, correction and verification. If those steps take three hours, the four-hour estimate leaves one hour of margin before allowing for uncertainty. A warning at 80% and a critical level at 90% leave approximately four and two hours respectively under these assumptions. A three-hour action cannot wait for the critical level. An escalation plan needs a faster mitigation or an earlier threshold.

These 80 and 90 thresholds are not the Oracle defaults. At the same 85% usage, a slow-growing 20 TB tablespace and a fast-growing 2 GB tablespace can have very different response windows. With fixed decimal capacities, their remaining space is 3 TB and 0.3 GB respectively. Growth rate and response requirements still determine the urgency.

See managing tablespace alerts.

Check what the percentage measures

DBA_TABLESPACE_USAGE_METRICS reports USED_SPACE and TABLESPACE_SIZE in data blocks. USED_PERCENT relates used space to the maximum possible tablespace size. For files with autoextend disabled, the denominator is the combined current file size. With autoextend enabled, Oracle considers potential growth, underlying storage free space and, where applicable, a nonzero MAX_PDB_STORAGE limit. The maximum possible size can change as shared storage is consumed.

Read the metric and the data files for the tablespace:

SELECT tablespace_name, used_space, tablespace_size, used_percent
FROM dba_tablespace_usage_metrics
WHERE tablespace_name = 'APP_DATA';

SELECT file_id, bytes, autoextensible, maxbytes, increment_by
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id;

BYTES describes the current file size. MAXBYTES bounds autoextension. INCREMENT_BY is in Oracle blocks. Convert block counts with the relevant tablespace block size when calculating bytes. A nondefault block-size tablespace needs its own block size. Do not treat a raw block count as GB.

Use file-system or ASM measurements to establish actual underlying free storage. A file's configured maximum does not reserve that storage. If other pluggable databases share the device, their growth matters too. Capture the relevant PDB storage limits. DBA_FREE_SPACE can help describe free extents in currently allocated permanent files. It measures a different boundary from the maximum-possible-size denominator above.

A missing row or an inaccessible view requires checking the container, tablespace type, privileges and metric availability. Do not infer that capacity is healthy from missing evidence.

See DBA_TABLESPACE_USAGE_METRICS and managing tablespaces.

Preserve and configure the threshold

The DBMS_SERVER_ALERT package requires the DBA or IMP_FULL_DATABASE role. Tablespace thresholds can be configured in the applicable PDB. Oracle documents root-only restrictions for non-tablespace thresholds. Check the STATUS and object scope in DBA_THRESHOLDS. Database-wide alerts use instance_name => NULL. Inventing an instance name can create a visible threshold that no instance uses. Dictionary-managed tablespaces are outside the package's documented tablespace object support.

Capture every original value with GET_THRESHOLD, including operators, values, observation period and occurrence count. Preserve the object and instance arguments too. SQL*Plus bind variables retain these values only for the current session, so also save a record of the original arguments.

VARIABLE old_warning_operator NUMBER
VARIABLE old_warning_value VARCHAR2(256)
VARIABLE old_critical_operator NUMBER
VARIABLE old_critical_value VARCHAR2(256)
VARIABLE old_period NUMBER
VARIABLE old_occurrences NUMBER

BEGIN
  DBMS_SERVER_ALERT.GET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => :old_warning_operator,
    warning_value => :old_warning_value,
    critical_operator => :old_critical_operator,
    critical_value => :old_critical_value,
    observation_period => :old_period,
    consecutive_occurrences => :old_occurrences,
    instance_name => NULL,
    object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name => 'APP_DATA');
END;
/

PRINT old_warning_operator old_warning_value
PRINT old_critical_operator old_critical_value
PRINT old_period old_occurrences

The package's general object-type tables and the tablespace-alert guidance both document tablespace support. The GET_THRESHOLD parameter table gives a narrower system and service description.

This call sets one tablespace's percent-full policy:

BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    warning_value => '80',
    critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    critical_value => '90',
    observation_period => 1,
    consecutive_occurrences => 1,
    instance_name => NULL,
    object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name => 'APP_DATA');
END;
/

TABLESPACE_PCT_FULL selects the percent metric. OPERATOR_GE includes equality, so values at or above the configured thresholds meet the comparisons. OBJECT_TYPE_TABLESPACE and object_name identify the affected object. NULL makes the instance argument database-wide within the applicable scope. The call configures alerting. It does not add storage or correct space exhaustion.

The package describes observation_period as a comparison interval of 1–60 minutes and consecutive_occurrences as the number of successive violating periods required before issue. Both are one here. Metric evaluation, collection and notification have their own schedules, so this configuration is not a promise of one-minute end-to-end delivery. Keep enough headroom for those delays.

Verify the configuration:

SELECT metrics_name, warning_operator, warning_value,
       critical_operator, critical_value,
       observation_period, consecutive_occurrences,
       instance_name, object_type, object_name, status
FROM dba_thresholds
WHERE object_type = 'TABLESPACE'
  AND object_name = 'APP_DATA';

Inspect the metric name and both percent and free-space thresholds if more than one row applies. A successful configuration call and a valid row establish configuration. Actual alert evaluation and delivery remain separate checks. Checking the package and DBA_THRESHOLDS does not require AWR, ADDM or a pack-only monitoring screen. An external monitoring product has its own deployment and licensing requirements.

See DBMS_SERVER_ALERT and DBA_THRESHOLDS.

Respond to the condition and verify resolution

SELECT object_name, reason, suggested_action
FROM dba_outstanding_alerts
WHERE object_name = 'APP_DATA';

REASON describes the alert and SUGGESTED_ACTION provides advice. Match the object and container to current measurements before choosing a change. A file that has reached its configured ceiling while storage is available may need a controlled file-capacity change. Exhausted shared storage requires restoring storage headroom or safely reducing demand. Increasing a file maximum cannot create free physical storage.

After a correction, record the actual usable capacity, remaining headroom and current growth. Confirm that the application can make its required allocations. Deleting table rows may leave extents allocated, so verify the metric and actual allocation behavior rather than assuming a row deletion increases tablespace free space.

A stateful space alert tracks an ongoing condition. Once it resolves, allow the metric's reevaluation and clearing cycle, check the same object and container, and inspect the current outstanding condition again. History is time-limited:

SELECT object_name, reason, creation_time, resolution
FROM dba_alert_history
WHERE object_name = 'APP_DATA'
ORDER BY creation_time DESC;

DBA_ALERT_HISTORY includes alerts that are no longer outstanding. RESOLUTION can be Cleared or N/A. Stateless alerts record events without a continuing threshold condition to return to normal. Use the history entry to understand the event or clearing transition. Current capacity and outstanding-alert evidence establish the present condition. Retain needed incident evidence before its time-limited history is purged.

Oracle's server alert queue and an external notification route require a consumer and operational configuration. Assign an operator, an acknowledgement expectation and an escalation path, then prove delivery and response timing.

See DBA_OUTSTANDING_ALERTS, DBA_ALERT_HISTORY, server alerts and notification, and stateful and stateless alerts.

Recap

Estimate time to capacity from remaining usable space and recent growth, then set warning and critical levels so the full response path still has margin. Read USED_PERCENT against the maximum possible tablespace size, including autoextend, shared storage and any applicable PDB limit. Save the current DBMS_SERVER_ALERT threshold before changing it. An observation period and occurrence count of one set the comparison rule. Evaluation and notification follow their own schedules. After a correction, confirm usable capacity and successful allocations, then check the same object in outstanding alerts and in time-limited history.

Quiz

1. Does 85% usage imply equal urgency for a slow-growing 20 TB tablespace and a fast-growing 2 GB tablespace?

Yes, the percentages are identical. Yes, critical alerts always arrive immediately. No; free capacity, growth, limits and response time determine urgency. Only the tablespace name matters.

2. With fixed usable capacity of 10 GB, 8 GB consumed and net growth of 0.5 GB/hour, what is the approximate time to capacity under those assumptions?

4 hours. 16 hours. 0.25 hours. Exactly 1 minute.

3. What can the denominator of USED_PERCENT reflect when autoextend is enabled?

Only the current size of the first data file. Maximum possible growth, underlying storage availability and applicable PDB limits. The number of connected users. An unconditional reservation of every byte in MAXBYTES.

4. What does setting both observation period and occurrence count to one establish?

A guaranteed notification exactly one minute later. That all original thresholds can be discarded. That the underlying space condition is corrected. The requested comparison interval and number of successive violations; actual evaluation and delivery timing must be checked.

5. What supports closing a stateful tablespace alert after a correction?

Removing an event from history. Verifying usable headroom and allocation behavior, then checking same-scope reevaluation, outstanding condition and history. Raising the threshold until the warning disappears. Assuming every history row describes a current condition.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily