前段時間通過dba_outstanding_alerts發現表空間增長超過了85%，然後加了個裸裝置做為資料檔案，雖然這個資料檔案可用，但dba_outstanding_alerts中的警告資訊並沒有消失：

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') cdate
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE CDATE
-------------------------------------------------- ------------ -----------------------
Tablespace [ERP_INDEX] is [94 percent] full          94.5023148 26-MAR-2010 19:45:05

可見Oracle在這裡認為這個表空間已經使用了94%以上，檢視一下預警閥值的設定：

SQL> col METRICS_NAME for a50

SQL> col WARN_VAL for a10
SQL> col CRIT_VAL for a10
SQL> col OBJ_TYPE for a20
SQL> SELECT METRICS_NAME
2  , WARNING_VALUE WARN_VAL
3  , CRITICAL_VALUE CRIT_VAL
4  , OBJECT_TYPE OBJ_TYPE
5  FROM SYS.DBA_THRESHOLDS
6  WHERE metrics_name LIKE '%Tablespace%';

METRICS_NAME                   WARN_VAL   CRIT_VAL   OBJ_TYPE
------------------------------ ---------- ---------- --------------------
Tablespace Bytes Space Usage   0          0          TABLESPACE
Tablespace Space Usage         85         97         TABLESPACE

超過85%就提示，似乎也是順利成章的，可是實際表空間到底佔用了多少呢：

SQL> select sum(bytes/1024/1024) from dba_data_files
2  where TABLESPACE_NAME='ERP_INDEX';

SUM(BYTES/1024/1024)
--------------------
31744

SQL> select sum(bytes/1024/1024) from sys.dba_free_space where tablespace_name='ERP_INDEX'
2  /

SUM(BYTES/1024/1024)
--------------------
3936

SQL>
SQL> select (31744-3936)/31744 from dual;

(31744-3936)/31744
------------------
.876008065

可見表空間實際的使用率是87%左右，那麼為什麼dba_outstanding_alerts中會提示是使用了94%呢？從DBA_TABLESPACE_USAGE_METRICS這個oracle 10g新增的未publish的檢視來看，使用率也是94%：

SQL> SELECT TABLESPACE_NAME TBSP_NAME
2  , USED_SPACE*8/1024
3  , TABLESPACE_SIZE*8/1024 TBSP_SIZE
4  , USED_PERCENT
5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS where TABLESPACE_NAME='ERP_INDEX';

TBSP_NAME                      USED_SPACE*8/1024  TBSP_SIZE USED_PERCENT
------------------------------ ----------------- ---------- ------------
ERP_INDEX                                  30016      31744   94.5564516

而這裡顯示為94%的原因是因為它統計了recyclebin裡佔用的空間：

SQL> connect banping/banping
Connected.
SQL> select sum(space*8/1024) from user_recyclebin where ts_name='ERP_INDEX';

SUM(SPACE*8/1024)
-----------------
2240

SQL> select (31744-3936+2240)/31744 from dual;

(31744-3936+2240)/31744
-----------------------
.946572581

可見，實際使用空間加上recyclebin裡的這個表空間的物件大小正好是94%左右。那麼purge了recyclebin後，是不是就解決了問題呢？

SQL> purge recyclebin;

Done

SQL> SELECT TABLESPACE_NAME TBSP_NAME
2  , USED_SPACE*8/1024
3  , TABLESPACE_SIZE*8/1024 TBSP_SIZE
4  , USED_PERCENT
5  FROM SYS.DBA_TABLESPACE_USAGE_METRICS where TABLESPACE_NAME='ERP_INDEX';

TBSP_NAME                      USED_SPACE*8/1024  TBSP_SIZE USED_PERCENT
------------------------------ ----------------- ---------- ------------
ERP_INDEX                                  27776      31744   87.5

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , sequence_id,reason_id
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE  sequence_id reason_id
-------------------------------------------------- ------------ --------------- ----------
Tablespace [ERP_INDEX] is [94 percent] full          94.5023148  566360 9

可見這樣操作能夠消除DBA_TABLESPACE_USAGE_METRICS檢視的錯誤統計，而dba_outstanding_alerts中的警告卻依然如故。嘗試看一下dba_outstanding_alerts的基表：

CREATE OR REPLACE VIEW SYS.DBA_OUTSTANDING_ALERTS AS
SELECT sequence_id,
reason_id,
owner,
object_name,
subobject_name,
typnam_keltosd AS object_type,
dbms_server_alert.expand_message(userenv('LANGUAGE'),
mid_keltsd,
reason_argument_1,
reason_argument_2,
reason_argument_3,
reason_argument_4,
reason_argument_5) AS reason,
time_suggested,
creation_time,
dbms_server_alert.expand_message(userenv('LANGUAGE'),
amid_keltsd,
action_argument_1,
action_argument_2,
action_argument_3,
action_argument_4,
action_argument_5)
AS suggested_action,
advisor_name,
metric_value,
decode(message_level, 32, 'Notification', 'Warning')
AS message_type,
nam_keltgsd AS message_group,
message_level,
hosting_client_id,
mdid_keltsd AS module_id,
process_id,
host_id,
host_nw_addr,
instance_name,
instance_number,
user_id,
execution_context_id,
error_instance_id
FROM wri$_alert_outstanding, X$KELTSD, X$KELTOSD, X$KELTGSD,
dba_advisor_definitions
WHERE reason_id = rid_keltsd
AND otyp_keltsd = typid_keltosd
AND grp_keltsd = id_keltgsd
AND aid_keltsd = advisor_id(+)

資訊來自wri$_alert_outstanding表，和X$KELTSD, X$KELTOSD, X$KELTGSD等底層表都有關聯，擔心直接刪掉的話會出問題，於是嘗試修改下閥值：

SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value=>95,
critical_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value=>97,
observation_period=>1,
consecutive_occurrences=>1,
instance_name=>NULL,
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name=>NULL);

PL/SQL procedure successfully completed

因為告警資訊消除後，會從dba_outstanding_alerts轉移到DBA_ALERT_HISTORY檢視，檢視這兩個檢視，果然發現了資訊已經轉移到了DBA_ALERT_HISTORY，而且提示的空間使用率已經變成了正確的87%，看來在這個過程中oracle會去某個地方重新取一次這個資訊：

select * from dba_alert_history where sequence_id=566360 and reason_id=9

SQL> SELECT REASON
2  , METRIC_VALUE
3  FROM SYS.DBA_ALERT_HISTORY where sequence_id=566360 and reason_id=9;

REASON                                             METRIC_VALUE
-------------------------------------------------- ------------
Tablespace [ERP_INDEX] is [87 percent] full          87.5

再把告警閥值更改回原來的85%，發現告警資訊又會出現在dba_outstanding_alerts中，不過已經是正確的87%了，而DBA_ALERT_HISTORY中的歷史資訊還是存在的，至此曲線解決了這個問題。

SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id=>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value=>85,
critical_operator=>DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value=>97,
observation_period=>1,
consecutive_occurrences=>1,
instance_name=>NULL,
object_type=>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name=>NULL);

PL/SQL procedure successfully completed

SQL> col reason for a50
SQL> SELECT REASON
2  , METRIC_VALUE
3  , TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') cdate
4  FROM SYS.DBA_OUTSTANDING_ALERTS;

REASON                                             METRIC_VALUE CDATE
-------------------------------------------------- ------------ -----------------------
Tablespace [ERP_INDEX] is [87 percent] full          87.5  8-JUN-2010 16:45:05

metalink上有個文件列舉了一些解決表空間使用率提示的問題，不過都不適用於我這個案例：

Troubleshooting a Database Tablespace Used(%) Alert problem [ID 403264.1]