[原創] 利用Oracle metric(threshold)監控表空間

在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>