SYSMAN can be compared to a mothman in the movie titled The Mothman Prophecies because like the mothman, SYSMAN also seems like it is a dark, mysterious figure lurking in the background. SYSMAN, by way of introduction, is used in Oracle as the schema used for Enterprise Manager or Grid Control or Database Control, the exact name depending on the context of the application. Much of the data or content you see in Database Control comes from SYSMAN tables.
Using Oracle Enterprise Manager can be, without doubt, one of the most frustrating experiences in Oracle because of OEMs poor, non-intuitive, disjointed interface. From a Web development standpoint, as an example, how many different ways can you vary the location of a Home link? Somewhere between looking in the top left corner, the top right corner, and near the bottom center of a page (and thats just when a Home link is presented because in many pages there is not a direct link), you have to roll your eyes and wonder what Oracle Corporation was thinking when OEM was being developed.
Unfortunately, the interface isnt the worst problem. My number one pet peeve about OEM is its inability to clean up after itself, so to speak. Thats right; Im talking about the Alerts section of the home page. The figure below is part of whats contained in the Alerts section of my home computer.
On May 21st, we see that session 147 was blocking another session. That was over three months ago (as of the date of publication of this article). In the Alerts section of a production database, Ive seen messages linger for well over a year. Even after invalid objects have long since been compiled/made valid, OEM continues to display irrelevant information about the state affairs of a database. Messages about tablespaces crossing an alert threshold or how much space is remaining in the db_recovery_file_dest location are other examples of stale information OEM insists on surfacing in the Alerts section. The worst part about these messages is that OEM does not provide a means of dismissing them.
There are several ways in which stale messages can be removed from the Alerts section, but it is unlikely you would ever know how to do this via any published documentation.
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 tables primary key is a composite key, and uses TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, and SEVERITY_CODE.
You wont find the RAW values anywhere on the page, even if you look at the pages source. If you try that approach, mainly as an exercise to see what the underlying HTML code looks like, all youre 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 _<FROM WHERE>. 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.
The trigger deletes from three tables: MGMT_CURRENT_SEVERITY (and uses only the SEVERITY_GUID value), MGMT_SEVERITY_ANNOTATION, and MGMT_NOTIFICATION_LOG. The easiest way to delete from the driving table is to use a GUI tool such as SQL Manager or Toad. Delete the row, commit the transaction, refresh OEM, and the message will be gone.
The tablespace space usage message may not go away if you edit the storage, and the number of invalid objects by schema isnt accurate in that it may not list all schemas with invalid objects.
There doesnt seem to be any information on MetaLink about clearing stale messages from OEM, and the Enterprise Manager documentation appears to be limited to only clearing alert log type of alerts. Alert log errors/messages are fairly easy to clear or purge, and alerts of that nature may also appear as a message in the Alerts section on the home page. Clear or purge the alert log error and the message should go away.
If in doubt, dont delete from the table, but as you saw in this article, Oracle does make provisions for deletions and takes steps to delete data from other tables as well. After seeing how easy it is to perform some housekeeping on one table, SYSMAN shouldnt seem as mysterious as it may have been in the beginning. It would be better if OEM took care of this cleanup itself, so barring that, you have two options when it comes to seeing stale messages: live with them for an indeterminate amount of time, or take matters into your own hands and do the cleanup yourself.