This group of metrics gives you the availability and status of the managed database. Metrics include:Database Status、Database Process Check、aximum # of sessions since startup and Availability. 在Oracle10G中引入了metric，用来监控数据库表空间，如下实例：
1、确认现有的metric
SQL> conn / as sysdba
已连接。
SQL> select count(1) from dba_thresholds;
COUNT(1)
----------
22
2、创建表空间
SQL> select metric_id,metric_name from v$metricname where metric_name like'%space%'
2 ;
METRIC_ID METRIC_NAME
---------- ----------------------------------------------------------------
9001 Tablespace Bytes Space Usage
9000 Tablespace Space Usage
SQL> create tablespace tbs_lw datafile '/opt/oracle/oradata/charge/tbs_lw01.dbf' size 10m;
表空间已创建。
3、创建新的metric
SQL> BEGIN dbms_server_alert.set_threshold (dbms_server_alert.tablespace_pct_full,NULL, NULL, NULL, NULL, 1, 1, NULL, dbms_server_alert.object_type_tablespace, 'TBS_LW');END;
2 /
PL/SQL 过程已成功完成。
4、查看新创建的metric
SQL> select count(1) from dba_thresholds;
COUNT(1)
----------
23
SQL> SELECT warning_value, critical_value, status FROM dba_thresholds WHERE metrics_name = 'Tablespace Space Usage' AND object_name = 'TBS_LW';
WARNING_VALUE CRITICAL_VALUE STATUS
-------------------- -------------------- -------
80 95 VALID
5、模拟一个表数据增长达到threshold
SQL> create table t1 tablespace tbs_lw as select * from user_objects;
表已创建。
SQL> select count(*) from t1;
COUNT(*)
----------
30060
SQL> insert into t1 select * from t1 where rownum insert into t1 select * from t1 where rownum commit;
提交完成。
6、查看是否生效
SQL> SELECT reason, message_level,DECODE(message_level, 5, 'WARNING', 1, 'CRITICAL') ALERT_LEVEL FROM dba_outstanding_alerts WHERE object_name = 'TBS_LW';
REASON MESSAGE_LEVEL ALERT_LE
-------------------------------------------------- ------------- --------
表空间 [TBS_LW] 已占用 [90 Percent] 5 WARNING
7、停用metric
SQL> BEGIN dbms_server_alert.set_threshold (dbms_server_alert.tablespace_pct_full,dbms_server_alert.operator_do_not_check, '0',dbms_server_alert.operator_do_not_check, '0', 1, 1, NULL,dbms_server_alert.object_type_tablespace, 'TBS_LW');END;
2 /
PL/SQL 过程已成功完成。
SQL> SELECT warning_value, critical_value, status FROM dba_thresholds WHERE metrics_name = 'Tablespace Space Usage' AND object_name = 'TBS_LW';
WARNING_VALUE CRITICAL_VALUE STATUS
-------------------- -------------------- -------
0 0 VALID
8、取消metric
BEGIN dbms_server_alert.set_threshold (dbms_server_alert.tablespace_pct_full,NULL, NULL, NULL, NULL, 1, 1, NULL, dbms_server_alert.object_type_tablespace, 'TBS_LW');END;
2 /
PL/SQL 过程已成功完成。
SQL> SELECT warning_value, critical_value, status FROM dba_thresholds WHERE metrics_name = 'Tablespace Space Usage' AND object_name = 'TBS_LW';
未选定行