DBMS_SERVER_ALERT

Yesterday(Fridday and not working on Saturday), I configured four server-generated alerts on four database, which sends critical alert via SMS. Unfortunately , I didn't expect one of the databases was so busy in the night (DB Wait time critcal threshold exceeded) , SMS beep keeps coming. What a night.

The firewall port has number opened, caused I can't access DB Control 's web page. Only things at end is having SYSMAN and password. No choice have to try to explore using DBMS_SERVER_ALERT to temporarily increase the threshold.

Google ... 10g OEM document ...
Here is thing learned quickly.

select * from dba_thresholds where metrics_name='Database Wait Time Ratio';
--easy than using DBMS_SERVER_ALERT.GET_THRESHOLD
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.DATABASE_WAIT_TIME,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '95',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '99',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'orcl10g2',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL
);
END;
/

--for blocking session count
--session blocking for CUSPA
select * from dba_thresholds where metrics_name ='Blocked User Session Count';

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.BLOCKED_USERS,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '50',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '100',
observation_period => 1,
consecutive_occurrences => 15,
instance_name => 'orcl10g2',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SESSION,
object_name => NULL
);
END;
/

Hope I can sleep well tonight.

Ref Oracle 10g doc:
Enterprise Manager Oracle Database and Database-Related Metric Reference Manual
Database PL/SQL Packages and Types Reference