Thursday, March 5, 2009

show alert log contents with external table

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--------

No comments: