oracle实验记录 （使用dbms_server_alert设置threshold）

使用下面这个包来设置些alert

 

SQL> desc dbms_server_alert
FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2~~~~~~~~~展开 alert message
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 USER_LANGUAGE                  VARCHAR2                IN
 MESSAGE_ID                     NUMBER                  IN
 ARGUMENT_1                     VARCHAR2                IN
 ARGUMENT_2                     VARCHAR2                IN
 ARGUMENT_3                     VARCHAR2                IN
 ARGUMENT_4                     VARCHAR2                IN
 ARGUMENT_5                     VARCHAR2                IN
PROCEDURE GET_THRESHOLD~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~获取定义
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 METRICS_ID                     BINARY_INTEGER          IN
 WARNING_OPERATOR               BINARY_INTEGER          OUT
 WARNING_VALUE                  VARCHAR2                OUT
 CRITICAL_OPERATOR              BINARY_INTEGER          OUT
 CRITICAL_VALUE                 VARCHAR2                OUT
 OBSERVATION_PERIOD             BINARY_INTEGER          OUT
 CONSECUTIVE_OCCURRENCES        BINARY_INTEGER          OUT
 INSTANCE_NAME                  VARCHAR2                IN
 OBJECT_TYPE                    BINARY_INTEGER          IN
 OBJECT_NAME                    VARCHAR2                IN
PROCEDURE SET_THRESHOLD~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~设置
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 METRICS_ID                     BINARY_INTEGER          IN
 WARNING_OPERATOR               BINARY_INTEGER          IN
 WARNING_VALUE                  VARCHAR2                IN
 CRITICAL_OPERATOR              BINARY_INTEGER          IN
 CRITICAL_VALUE                 VARCHAR2                IN
 OBSERVATION_PERIOD             BINARY_INTEGER          IN
 CONSECUTIVE_OCCURRENCES        BINARY_INTEGER          IN
 INSTANCE_NAME                  VARCHAR2                IN
 OBJECT_TYPE                    BINARY_INTEGER          IN
 OBJECT_NAME                    VARCHAR2                IN
FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET
SQL>

先看下可以设置的
SQL> select metric_id,metric_name from v$metricname where metric_name like'%space%'
;

 METRIC_ID METRIC_NAME
---------- ----------------------------------------------------------------
      9001 Tablespace Bytes Space Usage
      9000 Tablespace Space Usage
可以看到以上2个是针对tablespace usage的
TABLESPACE_PCT_FULL
 Tablespace space usage
 % full
 
TABLESPACE_BYT_FREE
 Tablespace bytes space usage
 Kilobytes free
 

 

SQL> create tablespace test2 datafile 'd:\test2.dbf' size 5m;

表空间已创建。

看看对象类型的选择
OBJECT_TYPE_SYSTEM
 Metrics collected on the system level for each instance.
 
OBJECT_TYPE_FILE
 Metrics collected on the file level. These are used for AVERAGE_FILE_READ_TIME and AVERAGE_FILE_WRITE_TIME metrics.
 
OBJECT_TYPE_SERVICE
 Metrics collected on the service level. Currently ELAPSED_TIME_PER_CALL and CPU_TIME_PER_CALL are collected.
 
OBJECT_TYPE_TABLESPACE
 Metrics collected on the tablespace level.
 
OBJECT_TYPE_EVENT_CLASS
 Metrics collected on wait event class level. Currently supported metrics are AVG_USERS_WAITING and DB_TIME_WAITING.
 
OBJECT_TYPE_SESSION
 Metrics collected on the session level. Currently only BLOCKED_USERS is collected. The threshold can only be set at the instance level, which means that no

object name should be specified when setting the threshold for this type of metric.
 
关系运算
Relational Operators
OPERATOR_CONTAINS
 A metric value matching an entry in a list of threshold values is considered a violation.
 
OPERATOR_DO_NOT_CHECK
 The metric value is not compared to the threshold value, and no alerts are generated. Use this operator to disable alerts for a metric.
 
OPERATOR_EQ
 A metric value equal to the threshold value is considered a violation. =
 
OPERATOR_GE
 A metric value greater than or equal to the threshold value is considered a violation. >=
 
OPERATOR_GT
 A metric value greater than the threshold value is considered a violation.>
 
OPERATOR_LE
 A metric value less than or equal to the threshold value is considered a violation.<=
 
OPERATOR_LT
 A metric value less than the threshold value is considered a violation.<
 
OPERATOR_NE
 A metric value not equal to the threshold value is considered a violation.<>不等于
 

实际操作
SQL> ed
已写入 file afiedt.buf

  1  BEGIN
  2  DBMS_SERVER_ALERT.SET_THRESHOLD (
  3  metrics_id=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
  4  warning_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
  5  warning_value=>70,
  6  critical_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
  7  critical_value=>99,
  8  observation_period=>1,
  9  consecutive_occurrences=>1,INSTANCE_NAME=>'null',
 10  object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 11  object_name=>'TEST2');
 12* END;
SQL> /

PL/SQL 过程已成功完成。
SQL> select metrics_name, WARNING_OPERATOR,status from dba_thresholds where object_
name='TEST2';

METRICS_NAME                                                     WARNING_OPER
---------------------------------------------------------------- ------------
STATUS
-------
Tablespace Space Usage                                           GE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~查看设置
VALID

 

SQL> create table t1 (a char(2000),b char(2000),c char(2000)) tablespace test2;

表已创建。

  1  declare
  2  begin
  3  for i in 1..300 loop
  4  insert into t1 values('a','a','a')
  5  end loop;
  6  commit;
  7* end;
SQL> /

SQL> select reason,message_level from dba_outstanding_alerts where object_name='TES
T2';

REASON
--------------------------------------------------------------------------------
MESSAGE_LEVEL
-------------
表空间 [TEST2] 已占用 [80 percent]
            5

取消的 warning_operator ,critical_operator改为OPERATOR_DO_NOT_CHECK 值改为0 就可以
删除的话  set_threshold 所有都为NULL 就可以

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-614274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-614274/