Monitor alertlog from a table

Summary 

Instead of login in the unix and start doing vi and tail to see the contents of alert log you can store it in a table. Basically you take advantage of the external tables feature in Oracle 9i and afterwards. 

How to Find the path for background_dump_dest

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'background_dump_dest';

Create a directory for this path

CREATE OR REPLACE DIRECTORY alert_log_dir1 AS '/path…';

Create the external table

CREATE TABLE alert_log1 ( text VARCHAR2(4000) )
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY alert_log_dir1
ACCESS PARAMETERS (
records delimited BY newline
nobadfile
nodiscardfile
nologfile
)
LOCATION(alert_log_dir1:'alert_SID.log')
)
REJECT LIMIT UNLIMITED;

Now query the last 200 lines of alertlog

 
SELECT TEXT FROM
(SELECT ROWNUM ID, LPAD('---->',DECODE(YEAR,'2008',0,6)) || text text FROM
(SELECT ROWNUM, SUBSTR(text, 21,6) YEAR, text FROM (SELECT ROWNUM, text
FROM sys.ALERT_LOG1
--where text like '%INF%'
ORDER BY ROWNUM DESC)
WHERE ROWNUM < 200)) ORDER BY ID DESC;