Search Results sje-intg-sys-dual-pkg_package contents




DeepseekVIEW: SYS.DBA_TABLESPACE_THRESHOLDS in Oracle EBS 12.1.1 & 12.2.2

The SYS.DBA_TABLESPACE_THRESHOLDS view is a critical data dictionary component in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, providing administrators with proactive monitoring capabilities for tablespace storage utilization. This view is part of Oracle's Automatic Storage Management (ASM) and Database Control framework, enabling threshold-based alerts to prevent space-related outages.

Technical Overview

This read-only view displays threshold settings configured for tablespace space usage monitoring. It contains the following key columns:

  • TABLESPACE_NAME: Identifies the tablespace being monitored
  • WARNING_VALUE: Percentage threshold for warning alerts
  • CRITICAL_VALUE: Percentage threshold for critical alerts
  • STATUS: Indicates if thresholds are enabled (1) or disabled (0)
  • CON_ID: Container ID in multitenant architectures (relevant for 12.2.2)

EBS Implementation Context

In Oracle EBS environments, this view integrates with:

  1. Oracle Applications Manager (OAM): Provides the graphical interface for threshold configuration
  2. DB Control/Grid Control: Used for centralized monitoring across multiple EBS instances
  3. Concurrent Processing: Critical for preventing job failures due to space constraints

Configuration Best Practices

For EBS implementations, Oracle recommends:

Tablespace TypeWarning ThresholdCritical Threshold
APPS_TS_TX_DATA85%95%
APPS_TS_TX_IDX80%90%
APPS_TS_QUEUES75%85%
TEMP90%98%

Operational Considerations

Key operational aspects include:

  • Thresholds are enforced by the SMON background process
  • Alerts generate in DBA_OUTSTANDING_ALERTS when triggered
  • Historical data is maintained in DBA_ALERT_HISTORY
  • In 12.2.2, CDB-level views require container-specific queries

Maintenance Procedures

Common administrative tasks involve: 

-- Query current thresholds
SELECT tablespace_name, warning_value, critical_value
FROM sys.dba_tablespace_thresholds
WHERE tablespace_name LIKE 'APPS%';

-- Modify thresholds via DBMS_SERVER_ALERT
BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    warning_value => '85',
    critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    critical_value => '95',
    observation_period => 1,
    consecutive_occurrences => 1,
    instance_name => NULL,
    object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name => 'APPS_TS_TX_DATA');
END;

Version-Specific Notes

For EBS 12.2.2 in multitenant configurations:

  • Thresholds must be set at both CDB and PDB levels
  • The CON_ID column becomes essential for container-specific queries
  • RAC environments require instance-specific threshold analysis

This view forms a critical component of the Oracle EBS storage management framework, enabling proactive capacity planning and preventing application outages due to space exhaustion. Proper configuration aligns with Oracle's Maximum Availability Architecture (MAA) guidelines for EBS environments.