False Alerts Being Generated For A Tablespace

(Doc ID 764402.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 10.2.0.4 - Release: 10.1 to 10.2
Enterprise Manager for Oracle Database - Version: 10.1.0.5 to 10.2.0.4   [Release: 10.1 to 10.2]
Information in this document applies to any platform.

Symptoms

++ For a tablesapce, the warning and critical thresholds are set.

++ There is a false alert generated intermittently for a tablespace under SYS.DBA_OUTSTANDING_ALERTS view.  It indicates that the tablespace is almost full, exceeding the warning/critical threshold even though the actual tablespace usage when checked is less than the warning/critical threshold value.

++ The following set of queries mentioned below can be used to verify the difference in the tablespace usage metrics populated under DBA_TABLESPACE_USAGE_METRICS view when compared to the actual space used by the tablespace

SQL> SELECT REASON, METRIC_VALUE, MESSAGE_TYPE , MESSAGE_LEVEL, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'), HOST_ID FROM SYS.DBA_OUTSTANDING_ALERTS;

SQL> SELECT METRICS_NAME, WARNING_OPERATOR WARN_OP, WARNING_VALUE WARN_VAL, CRITICAL_OPERATOR CRIT_OP, CRITICAL_VALUE CRIT_VAL, OBJECT_TYPE OBJ_TYPE, STATUS FROM SYS.DBA_THRESHOLDS WHERE METRIC_NAME LIKE ‘%Tablespace%’ AND OBJECT_NAME LIKE '<tablespace_name>';

SQL> SELECT TABLESPACE_NAME TBSP_NAME, USED_SPACE, TABLESPACE_SIZE TBSP_SIZE, USED_PERCENT FROM SYS.DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME LIKE '<tablespace_name>';

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME LIKE '<tablespace_name>';

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '<tablespace_name>';

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME LIKE '<tablespace_name>';

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_EXTENTS WHERE TABLESPACE_NAME LIKE '<tablespace_name>';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms