Re: Alert log File (External tables)

Hi

If one creates a permanent table alert_log with a logline column varchar2(4000) on 9.2 then I believe this should illustrate a second approach for the original poster. The idea is to run it once a day to populate a permanent table with the contents of the alert log and then rename the alert log. If you wish to monitor more frequently, which would be a good idea on a production system then it would make sense to change the date format mask for the rename.

create or replace procedure read_alertlog(p_filename IN VARCHAR2,p_location in VARCHAR2)

as

/*

purpose: procedure to read alert log at a daily interval.

requirements: Directory object created for the background_dump_dest.

read permissions on this directory.

table called alert_log, column logline varchar2(4000).

parameters: name of alert log, name of bdump directory.

Version: NL 27/11/2003 - created.

NL 02/12/2003 - generalized.

*/

fHandle UTL_FILE.FILE_TYPE;

v_logline VARCHAR2(4000);

v_newname varchar2(50);

BEGIN fHandle := UTL_FILE.FOPEN(p_location,p_filename,'r',4000);

loop

begin

UTL_FILE.get_line(fHandle,v_logline);

insert into alert_log(logline) values(v_logline);

exception -- reached end of file

when no_data_found then exit;

end;

end loop;

UTL_FILE.FCLOSE(fHandle);

v_newname := p_filename||to_char(sysdate,'DDMMYY');

UTL_FILE.FRENAME(p_location,p_filename,p_location,v_newname);

end;

/

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

"Ron" <support_at_dbainfopower.com> wrote in message
news:odqdncakO431_LDd4p2dnA_at_comcast.com...


> Hello Daniel,

>

>   Appreciate you critics.

>

>   You are more then welcome to provide user with the 100% working

solution.


>

>   IMHO: having provided sample user can customize it the way he needs it,

>

> Regards,

>

>   Ron

>   DBA Infopower

>   http://www.dbainfopower.com

>   Standard disclaimer:

> http://www.dbainfopower.com/dbaip_advice_disclaimer.html

>

>

>

> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message

> news:1076688960.210119_at_yasure...

> > Ron wrote:

> >

> > > Here you go:

> > >

> > > 1.  create directory BDUMP as '...';

> > >

> > > 2. create table alert_log ( text varchar2(80) )

> > > organization external (

> > > type oracle_loader

> > > default directory BDUMP

> > >     access parameters (

> > >         records delimited by newline

> > >     )

> > > location('<alert.log>')

> > > )

> > > reject limit 1000;

> > > Regards,

> > >

> > >   Ron

> >

> > Alert log lines can be very substantially longer than 80 characters.

> > Have you atually used this and if so how could it have worked?

> >

> > Here are a few lines from my alert log

> >

> >    control_files            = C:\OGRID\GRID\CONTROL01.CTL,

> > C:\OGRID\GRID\CONTROL02.CTL, C:\OGRID\GRID\CONTROL03.CTL

> >

> > starting up 1 dispatcher(s) for network address

> > '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

> >

> > db_recovery_file_dest_size of 2147483648 bytes is 0.00% used, and has

> > 2147483648 remaining bytes available.

> >

> > Note that these are allsingle lines.

> >

> > -- 

> > Daniel Morgan

> > http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

> > http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

> > damorgan_at_x.washington.edu

> > (replace 'x' with a 'u' to reply)

> >

>

>