Oracle Tablespace Thresholds Reset Tool
Resets or updates tablespace alert thresholds (warning and critical) using DBMS_SERVER_ALERT. Can be used to apply new percentage-full limits or reset thresholds back to default values for locally managed tablespaces.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads2 views0 comments
By OracleDba • Created
Code
(51 lines)123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/ts_thresholds_reset.sql
-- Author : Tim Hall
-- Description : Displays threshold information for tablespaces.
-- Call Syntax : @ts_thresholds_reset (warning) (critical)
-- @ts_thresholds_reset NULL NULL -- To reset to defaults
-- Last Modified: 13/02/2014 - Created
-- -----------------------------------------------------------------------------------
SET VERIFY OFF
DECLARE
g_warning_value VARCHAR2(4) := '&1';
g_warning_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
g_critical_value VARCHAR2(4) := '&2';
g_critical_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
PROCEDURE set_threshold(p_ts_name IN VARCHAR2) AS
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => g_warning_operator,
warning_value => g_warning_value,
critical_operator => g_critical_operator,
critical_value => g_critical_value,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => p_ts_name);
END;
BEGIN
IF g_warning_value = 'NULL' THEN
g_warning_value := NULL;
g_warning_operator := NULL;
END IF;
IF g_critical_value = 'NULL' THEN
g_critical_value := NULL;
g_critical_operator := NULL;
END IF;
FOR cur_ts IN (SELECT tablespace_name
FROM dba_tablespace_thresholds
WHERE warning_operator != 'DO NOT CHECK'
AND extent_management = 'LOCAL')
LOOP
set_threshold(cur_ts.tablespace_name);
END LOOP;
END;
/
SET VERIFY ON