DBA Hub

📋Steps in this guide1/4

Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)

Set tablespace thresholds using the DBMS_SERVER_ALERT package as an early warning mechanism for space issues.

oracle miscconfigurationintermediate
by OracleDba
20 views
1

Introduction

Cloud Control no longer uses these server thresholds. Changes to thresholds in the Cloud Control interface are still reflected in the view on the database, but it doesn't use the database as a source for these thresholds. As a result, if you are using Cloud Control for threshold notifications, you should change settings there, not directly using the package. Oracle allows you to set tablespace thresholds using the package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by some versions of Enterprise Manager (DB Control, Grid Control or Cloud Control). Setting the parameter to NULL sets the default threshold for all tablespace in the database. Setting the parameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting. There are two types of tablespace thresholds that can be set. - : Percent full. When the warning or critical threshold based on percent full is crossed a notification occurs. - : Free Space Remaining (KB). The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example Cloud Control displays and sets these values in MB. The thresholds are set using a value and an operator. - : Less than or equal. - : Greater than or equal.
2

Setting Thresholds

Make a note of your existing thresholds before changing them, so you know what to set them back to. The following examples show how to set the different types of alerts. Setting the warning and critical levels to '0' disables the notification.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
BEGIN
  -- Database-wide KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
    
  -- Database-wide percent full threshold.
  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          => '97',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);

  -- Tablespace-specific KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '90',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '98',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific reset to defaults.
  -- Set warning and critical values to NULL.
  --DBMS_SERVER_ALERT.set_threshold(
  --  metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
  --  warning_operator        => DBMS_SERVER_ALERT.operator_ge,
  --  warning_value           => NULL,
  --  critical_operator       => DBMS_SERVER_ALERT.operator_ge,
  --  critical_value          => NULL,
  --  observation_period      => 1,
  --  consecutive_occurrences => 1,
  --  instance_name           => NULL,
  --  object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
  --  object_name             => 'USERS');
END;
/
3

Displaying Thresholds

The threshold settings can be displayed using the view. Oracle 11g Release 2 introduced the view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

TABLESPACE_NAME                METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ ------------------------------ ------------ ------------------------------ ------------ ---------------
TEMP                           Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
UNDOTBS1                       Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
UNDOTBS2                       Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
USERS                          Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          Tablespace Space Usage         GE           90                             GE           98
                               Tablespace Space Usage         GE           85                             GE           97
                               Tablespace Bytes Space Usage   LE           1024000                        LE           102400

7 rows selected.

SQL>

SET LINESIZE 200

COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT tablespace_name,
       contents,
       extent_management,
       threshold_type,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_tablespace_thresholds
ORDER BY tablespace_name;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN THRESHOL METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
TEMP                           TEMPORARY LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO NOT CHECK 0
UNDOTBS1                       UNDO      LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO NOT CHECK 0
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Space Usage         GE           90                             GE           98

10 rows selected.

SQL>
4

Scripts

You may find the following scripts useful. - ts_thresholds.sql - ts_thresholds_reset.sql - ts_thresholds_set_default.sql For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!