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/，如需轉載，請註明出處，否則將追究法律責任。