ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (使用dbms_server_alert设置threshold)

oracle实验记录 (使用dbms_server_alert设置threshold)

原创 Linux操作系统 作者:冷月逐浪 时间:2011-07-17 19:52:11 0 删除 编辑

转自:http://space.itpub.net/12020513/viewspace-614274

使用下面这个包来设置些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/20709695/viewspace-702408/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-07-10

  • 博文量
    42
  • 访问量
    82792