Monitor alert.log 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.

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 alert.log:

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;