BORRAR NOTIFICACIONES ENTERPRISE MANAGER

Si deseamos borrar todo el historial de notificaciones:
SQL> begin
2 delete from mgmt_current_severity;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

begin
delete from mgmt_current_severity;
end;

The Driving Table

The table of interest in the SYSMAN schema is named MGMT_SEVERITY.

USER is "SYSTEM"
SQL> conn sysman/oracle
Connected.
USER is "SYSMAN"
SQL> desc mgmt_severity
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 TARGET_GUID                               NOT NULL RAW(16)
 METRIC_GUID                               NOT NULL RAW(16)
 KEY_VALUE                                 NOT NULL VARCHAR2(256)
 COLLECTION_TIMESTAMP                      NOT NULL DATE
 LOAD_TIMESTAMP                                     DATE
 SEVERITY_CODE                             NOT NULL NUMBER
 SEVERITY_TYPE                                      NUMBER
 SEVERITY_DURATION                                  NUMBER
 SEVERITY_GUID                                      RAW(16)
 ANNOTATED_FLAG                                     NUMBER
 NOTIFICATION_STATUS                                NUMBER
 MESSAGE                                            VARCHAR2(4000)
 MESSAGE_NLSID                                      VARCHAR2(64)
 MESSAGE_PARAMS                                     VARCHAR2(4000)
 ACTION_MESSAGE                                     VARCHAR2(4000)
 ACTION_NLSID                                       VARCHAR2(64)
 ACTION_MESSAGE_PARAMS                              VARCHAR2(4000)
 ADVISORY_ID                                        VARCHAR2(64)
 USER_NAME                                          VARCHAR2(64)

Aside from the RAW datatypes, working with data in this table should be fairly straightforward, that is, once you know what to look for. The table’s primary key is a composite key, and uses TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, and SEVERITY_CODE.

You won’t find the RAW values anywhere on the page, even if you look at the page’s source. If you try that approach, mainly as an exercise to see what the underlying HTML code looks like, all you’re going to see (at the bottom of the text editor page) are a few lines of code, with one of them stretching out well over 30,000 characters.

The two best telltales in the table are KEY_VALUE and COLLECTION_TIMESTAMP. The KEY_VALUE values have a pattern to them. If the message is about a tablespace having crossed a space threshold, then the KEY_VALUE is simply the name of the tablespace. If the message is about SYS having logged on, the KEY_VALUE will be a mixture of “SYS” plus “_.” The recovery area message is identified by a value of RECOVERY AREA. The blocking message starts with SID, and the invalid objects in a schema begins with the schema name.

For the most part, the date and time shown under Alert Triggered will appear as the value for COLLECTION_TIMESTAMP. Between that time and the “key values” in the KEY_VALUE column, you should be sufficiently armed to invoke a procedure named DELETE_CURRENT_SEVERITY in the EM_SEVERITY package.

PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN

This procedure deletes data from a table named MGMT_CURRENT_SEVERITY.

  BEGIN
    DELETE FROM MGMT_CURRENT_SEVERITY
     WHERE target_guid = p_target_guid
       AND metric_guid = p_metric_guid
       AND key_value = p_key_value;
  END delete_current_severity;

On the face of it, this approach seems like a safe way to go about clearing stale messages, but there is a better way, and that way is to delete directly from the MGMT_SEVERITY table and let the SEVERITY_DELETE trigger do all the work for you.