Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)
This article describes how to set tablespace thresholds using the
DBMS_SERVER_ALERTpackage as an early warning mechanism for space issues.Introduction
Oracle allows you to set tablespace thresholds using the
DBMS_SERVER_ALERT 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 the Enterprise Manager (DB Control, Grid Control or Cloud Control).
Setting the
OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAMEparameter 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.
TABLESPACE_PCT_FULL: Percent full. When the warning or critical threshold based on percent full is crossed a notification occurs.TABLESPACE_BYT_FREE: 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.
OPERATOR_LE: Less than or equal.OPERATOR_GE: Greater than or equal.
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.
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');
END;
/
Setting the warning and critical levels to '0' disables the notification.
Displaying Thresholds
The threshold settings can be displayed using the
DBA_THRESHOLDS view.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 DO_NOT_CHECK 0
UNDOTBS1 Tablespace Space Usage DO NOT CHECK DO_NOT_CHECK 0
UNDOTBS2 Tablespace Space Usage DO NOT CHECK 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>
Oracle 11g Release 2 introduced the
DBA_TABLESPACE_THRESHOLDS view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.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 DO NOT CHECK 0
UNDOTBS1 UNDO LOCAL EXPLICIT Tablespace Space Usage DO NOT CHECK 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>
No hay comentarios:
Publicar un comentario