94 - [Oracle 19C] Monitoring Tablespace Space Usage

 

 

• Monitoring Tablespace Space Usage

 

 

 

 

The database server tracks space utilization while performing regular space management activities. This information is aggregated by the MMON process. An alert is triggered when the threshold for a tablespace has been reached or cleared.

The DBMS_SERVER_ALERT package enables you to configure the Oracle Database server to issue an alert when a threshold for a specified server metric has been violated. You can configure both warning and critical thresholds for a large number of predefined metrics. in this course we will focus on tablespace_pct_full metric

If a warning threshold is reached, the server generates a severity level 5 alert. If a critical threshold is reached, the server generates a severity level 1 alert.

 

 

 

 Read-only and offline tablespaces: Do not set up alerts.
 Temporary tablespace: Threshold corresponds to space currently used by sessions.
 Undo tablespace: Threshold corresponds to space used by active and unexpired extents.
 Auto-extensible files: Threshold is based on the maximum file size

 

 

 

DBMS_SERVER_ALERT.SET_THRESHOLD
example
We have tablespace called TBSALERT
We need to set warning at 55%
We need to set critical at 70%

 

 

begin
  DBMS_SERVER_ALERT .SET_THRESHOLD(
  metrics_id => dbms_server_alert.tablespace_pct_full,
  warning_operator =>DBMS_SERVER_ALERT.OPERATOR_GE,
  warning_value => '55',
  critical_operator =>DBMS_SERVER_ALERT.OPERATOR_GE,
  critical_value => '70',
  observation_period => 1,
  consecutive_occurrences => 1,
  instance_name => 'orcl',
  object_type =>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
  object_name => 'TBSALERT');
end;

 

 

OPERATOR_GE: A metric value greater than or equal to the threshold value is considered a violation.

 

 

 

https://docs.oracle.com/database/121/ARPLS/d_server_alert.htm#ARPLS68004

DBMS_SERVER_ALERT

 

 

 

• Resolving Space Usage Issues

Resolve space usage issues by:
  Adding or resizing datafiles
  Setting AUTOEXTEND to ON
  Shrinking ojbects
  Reducing UNDO_RETENTION
  
Check for long-running queries in temporary tablespaces