oracle dbms_space,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 就可以