Oracle Database 11g -- Deleting a Threshold Results in ORA-13900: missing or invalid parameter warning operator

Problem

You are executing the DBMS_SERVER_ALERT.SET_THRESHOLD procedure to delete a threshold and get the error ORA-13900: missing or invalid parameter warning operator. For example,

SQL> begin
  2  DBMS_SERVER_ALERT.SET_THRESHOLD(
  3  metrics_id               => DBMS_SERVER_ALERT.AVG_FILE_READ_TIME,
  4  warning_operator         => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
  5  warning_value            => '0',
  6  critical_operator        => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
  7  critical_value           => '0',
  8  observation_period       => 8,
  9  consecutive_occurrences  => 1,
 10  instance_name            => 'orcl11g',
 11  object_type              => DBMS_SERVER_ALERT.OBJECT_TYPE_FILE,
 12  object_name              => '/u01/app/oracle/oradata/orcl11g/system01.dbf');
 13  end;  
 14  /
begin
*
ERROR at line 1:
ORA-13900: missing or invalid parameter warning operator
ORA-06512: at "SYS.DBMS_SERVER_ALERT", line 7
ORA-06512: at line 2

Solution

Use NULL for the operator and thresholds values. For example,

SQL> begin
  2  DBMS_SERVER_ALERT.SET_THRESHOLD(
  3  metrics_id               => DBMS_SERVER_ALERT.AVG_FILE_READ_TIME,
  4  warning_operator         => NULL,
  5  warning_value            => NULL,
  6  critical_operator        => NULL,
  7  critical_value           => NULL,
  8  observation_period       => 8,
  9  consecutive_occurrences  => 1,
 10  instance_name            => 'orcl11g',
 11  object_type              => DBMS_SERVER_ALERT.OBJECT_TYPE_FILE,
 12  object_name              => '/u01/app/oracle/oradata/orcl11g/system01.dbf');
 13  end;   
 14  /

PL/SQL procedure successfully completed.

SQL>