Select from Alert and Listener logs using V$DIAG_ALERT_EXT

With Oracle 11.2 it is now possible to select directly from the alert and listener logs.

This is quite useful if a DBA is logged into the database, but not the server, and wants to monitor logs.

# Selecting from the Alert log :-

SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT

from V$DIAG_ALERT_EXT

WHERE ORIGINATING_TIMESTAMP > sysdate -1/24

and trim(COMPONENT_ID)='rdbms'; 

ORIGINATING_TIMESTAMP

-----------------------------------------------------------------

MESSAGE_TEXT

-----------------------------------------------------------------

29-JAN-13 12.19.40.633000000 PM +09:00

create tablespace HR_AUDIT

29-JAN-13 12.19.42.037000000 PM +09:00

Completed: create tablespace HR_AUDIT

# Selecting from the Listener log :-


SQL> select ORIGINATING_TIMESTAMP,MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate -1/240
and trim(COMPONENT_ID)='tnslsnr';  

ORIGINATING_TIMESTAMP
-----------------------------------------------------------------
MESSAGE_TEXT
-----------------------------------------------------------------
29-JAN-13 12.20.22.866000000 PM +09:00
29-JAN-2013 12:20:22 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15961))
 * establish * test * 0

29-JAN-13 12.20.23.540000000 PM +09:00
29-JAN-2013 12:20:23 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15962))
 * establish * test * 0