Monitor alertlog not from a file, but 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. 

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_dir AS '/path…';

Create the external table

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

Now query the last 200 lines of alertlog

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