show alert log contents with external table

Reference:
http://halimdba.blogspot.com/2009/03/show-alertlog-contents-without-db.html

show alert log contents with external table

1. Run the procedure with system user or (any privilige user)


DECLARE
BDumpDir VARCHAR2(200);
SID VARCHAR2(16);
ObjectExists EXCEPTION;
PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
------ get the bdump dir
SELECT value
INTO BDumpDir
FROM v$parameter
WHERE name='background_dump_dest';

----- create the directory for the bdump dir
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
BDumpDir||'''';
----- grant the necessary privileges
EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
------ get the SID
SELECT instance_name INTO SID FROM v$instance;
----- create the external table
EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL
(TEXT VARCHAR2(255)
) ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY BDUMP_DIR
ACCESS PARAMETERS
(records delimited by newline
nobadfile
nologfile
)
LOCATION (''alert_'||SID||'.log'')
)
REJECT LIMIT UNLIMITED'
;
------ ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/


2. Now we can query the external table to read the alert log contents.


a. show from last but in ascending order (this is nice and real)
----------------------------------------------------------------

select text from(
select rownum s,text from (
SELECT rownum,text FROM system.alert_log_external
order by rownum desc
)
where rownum<200
)
order by s desc


b. show from last
-------------------

select rownum,text from (
SELECT * FROM system.alert_log_external
order by rownum desc
)


c. show with like condition
---------------------------

select rownum,text from (
SELECT * FROM system.alert_log_external
order by rownum desc
)
where upper(text) like upper('%ERROR%')

---------------end--------