在10g中引入了metric,不但可以调化数据库,还可以用来监控数据库,如下

1.创建测试表空间
SYS@DBAtest>create tablespace TBS_YXYUP datafile ‘/u01/oracle/oradata/dbatest/tbs_yxyup01.dbf’ size 10m;

Tablespace created.

Elapsed: 00:00:01.09

2.确认现有metric
SYS@dbatest>select count(1) from dba_thresholds;

COUNT(1)
———-
22

Elapsed: 00:00:00.04

3.创建新的metric

SYS@dbatest>BEGIN
2 dbms_server_alert.set_threshold(
3 dbms_server_alert.tablespace_pct_full,
4 dbms_server_alert.operator_ge, 80,
5 dbms_server_alert.operator_ge, 95, 1, 1, NULL,
6 dbms_server_alert.object_type_tablespace, ‘TBS_YXYUP’);
7 END;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

4.查看新创建的metric
SYS@dbatest>select count(1) from dba_thresholds;

COUNT(1)
———-
23

Elapsed: 00:00:00.01

Elapsed: 00:00:00.00
SYS@dbatest>col warning_value format a20
SYS@dbatest>col critical_value format a20
SYS@dbatest>
SYS@dbatest>SELECT warning_value, critical_value, status
2 FROM dba_thresholds
3 WHERE metrics_name = ‘Tablespace Space Usage’
4 AND object_name = ‘TBS_YXYUP’;

WARNING_VALUE CRITICAL_VALUE STATUS
——————– ——————– ——-
80 95 VALID

Elapsed: 00:00:00.00
SYS@dbatest>

5.模拟数据增长,达到threshold

YXYUP@dbatest>drop table t3 purge;

Table dropped.

Elapsed: 00:00:00.10
YXYUP@dbatest>create table t3 tablespace tbs_YXYUP as select * from dba_objects;

Table created.

Elapsed: 00:00:00.90
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.03
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.02
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.03
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.00
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.02
YXYUP@dbatest>r
1* insert into t3 select * from t3 where rownum<4001

4000 rows created.

Elapsed: 00:00:00.03
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.03
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.01
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.03
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.01
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.00
YXYUP@dbatest>insert into t3 select * from t3 where rownum<4001 ;

4000 rows created.

Elapsed: 00:00:00.02
YXYUP@dbatest>commit;

Commit complete.

Elapsed: 00:00:00.01
YXYUP@dbatest>

SYS@dbatest>select sum(bytes)/1024/1024 from dba_segments where segment_name=’T3′

SUM(BYTES)/1024/1024
——————–
9

6. 查看是否生效

SYS@dbatest>col REASON for a50;
SYS@dbatest>SELECT reason, message_level,
2 DECODE(message_level, 5, ‘WARNING’, 1, ‘CRITICAL’) ALERT_LEVEL
3 FROM dba_outstanding_alerts
4 WHERE object_name = ‘TBS_YXYUP’;

REASON MESSAGE_LEVEL ALERT_LE
————————————————– ————- ——–
Tablespace [TBS_YXYUP] is [90 percent] full 5 WARNING

7.停用metric
SYS@dbatest>BEGIN
2 dbms_server_alert.set_threshold (
3 dbms_server_alert.tablespace_pct_full,
4 dbms_server_alert.operator_do_not_check, ’0′,
5 dbms_server_alert.operator_do_not_check, ’0′, 1, 1, NULL,
6 dbms_server_alert.object_type_tablespace, ‘TBS_YXYUP’);
7 END;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SYS@dbatest>SELECT warning_value, critical_value, status
2 FROM dba_thresholds
3 WHERE metrics_name = ‘Tablespace Space Usage’
4 AND object_name = ‘TBS_YXYUP’;

WARNING_VALUE CRITICAL_VALUE STATUS
——————– ——————– ——-
0 0 VALID

8.取消metric(threshold)
SYS@dbatest>BEGIN
2 dbms_server_alert.set_threshold (
3 dbms_server_alert.tablespace_pct_full,
4 NULL, NULL, NULL, NULL, 1, 1, NULL,
5 dbms_server_alert.object_type_tablespace, ‘TBS_YXYUP’);
6 END;
7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SYS@dbatest>SELECT warning_value, critical_value, status
2 FROM dba_thresholds
3 WHERE metrics_name = ‘Tablespace Space Usage’
4 AND object_name = ‘TBS_YXYUP’;

no rows selected

Elapsed: 00:00:00.01
SYS@dbatest>