Monitoring Tablespace Usage in Oracle

Back to: Oracle DBA Tutorials

Monitoring Tablespace Usage in Oracle

In this article, I will explain Monitoring Tablespace Usage in Oracle with Examples. Please read our previous article discussing Advanced Row Compression in Oracle with Examples. The DBA needs to see some reports for the tablespaces.

There is a background process called MMON that generates alerts in the database. In the Oracle database, we can set the threshold values for warning and critical.

For example, there is a tablespace named USERS. We can set the warning threshold value as 85% and the critical threshold value as 95%. If the tablespace reaches 85% then the process MMON will raise a warning.

If the tablespace reaches 95%, the MMON process will send an alert as critical. As a DBA, we have to look at the alert and resolve it as soon as possible.

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

The DBMS_SERVER_ALERT package parameter enables you to configure the Oracle database server to issue an alert when a threshold for a specified server metric has been violated. The Oracle database has multiple metrics. Here, we will focus on the tablespace_pct_full. We can configure both warning and critical thresholds for a large number of predefined metrics.

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.

Note:

• Read-only and offline tablespaces: There is no need to set up alerts because the tablespaces are offline, and no one uses them.
• Temporary tablespace: The threshold corresponds to the space currently used by the sessions.
• Undo Tablespace: Threshold corresponds to space used by active and unexpired extents.
• Auto-extensible files: The threshold is based on the maximum file size.

Example:

There is a package called DBMS_SERVER_ALERT and a procedure called SET_THRESHOLD. We have to use SET_THRESHOLD to set the alert pattern for the tablespaces. There is a tablespace called TBSALERT. We have to set the warning at 55% and the 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=> 'JAYADB', 
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, 
object_name=> 'TBSALERT');
 end;

From the above PL/SQL block, we can see the metrics id is tablespace_pct_full.

The parameter OPERATOR_GE is defined as the metric value greater than or equal to the threshold value that is considered a violation. The warning_operator for the tablespace is set at 55%, which indicates that if the tablespace reaches 55% or more, a warning alert is generated. The critical_operator is set to 70% or more, which indicates that if the tablespace reaches 70, a critical alert is generated.

The object_type is DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE defines the object type for which we are setting up the alert, and the object_name indicates the tablespace name.

Resolving Space Usage Issue in Oracle:

For example, a DBA is working on other issues when suddenly an alert indicates that the tablespace has reached its threshold value of warning or critical. Methods to Resolve:

• Adding or resizing the datafiles
• Setting AUTOEXTEND to ON.
• Shrinking the Objects in the database.
• Reducing the UNDO_RETENTION.
• Check the long-running queries in the temporary tablespaces.

So, this is how the DBA will be resolving the tablespace issues and space issues by getting alerts.

In the next article, I will discuss DBMS_SERVER_ALERT in ORACLE with Examples. In this article, I explain Monitoring Tablespace Usage in Oracle with Examples. I hope you enjoy this article on Monitoring Tablespace Usage in Oracle.