there should be a commit in there as well...
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:40308acb$0$7074$ed9e5944_at_reading.news.pipex.net...
> 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)
> > >
> >
> >
>
>
Received on Mon Feb 16 2004 - 04:53:08 CST
