V$DIAG_ALERT_EXT table

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 21 Apr 2010 16:35:22 +0000 (UTC)
Message-ID: <pan.2010.04.21.16.35.22_at_email.here.invalid>

I must confess that this table has become slightly more useful in 11.2.0.1. with the April 2010 PSU applied. It is still not documented and the MOS search of V$DIAG_ALERT_EXT will not retrieve anything. It still has it's quirks:

	SQL> select count(*) from v$diag_alert_ext where 
	component_id='rdbms';
	select count(*) from v$diag_alert_ext where component_id='rdbms'
                     *
	ERROR at line 1:
	ORA-48207: Illegal Field Name [RDBMS]


	Elapsed: 00:00:00.40
	SQL> select count(*) from v$diag_alert_ext where component_id
	 like 'rdb%';

	  COUNT(*)

	----------
	     19686

	Elapsed: 00:00:03.89

Still, the quirks can now be bypassed by using "like" instead of the equality operator. There is also "message level" column which identified the most dangerous errors, ORA-7445 and ORA-0600 as "level 1" errors:

  1 select originating_timestamp,message_text   2 from v$diag_alert_ext
  3 where message_level=1 and

  4	   originating_timestamp > systimestamp - 15 and
  5*	   component_id like 'rdb%'

SQL> / ORIGINATING_TIMESTAMP

---

MESSAGE_TEXT

---

09-APR-10 06.52.52.124000000 PM -04:00
Errors in file /i01/app/oracle/diag/rdbms/vmso/VMSO/trace/ VMSO_ora_8615.trc (in
cident=28255):
ORA-07445: exception encountered: core dump [kkopmCheckSmbUpdate()+3032] [SIGSEG
V] [ADDR:0x20] [PC:0xD3C4EA0] [Address not mapped to object] []

Elapsed: 00:00:03.92

Most of the "normal errors", if such thing exists, are level 16. Basically, despite the little quirks, data mining through the V$DIAG_ALERT_EXT table is now possible. I consider this to be a great administration addition. Good job, Oracle. Please, fix the rest of the quirks and document this table. Good thing is that there are errors for the component "tnslsnr", which means that it is now possible to catch Oracle*Net errors, too.

-- 
http://mgogala.byethost5.com

Received on Wed Apr 21 2010 - 11:35:22 CDT