Monitoring Alert file

Monitoring Alert file

I would like to monitor ORA- messages on a Db whithout having access to the filesystem (to the alert file...)Is there a view which allows that or do you have an idea ?

There is no view but if you have an account with:
o select on v_$parametero select on v_$threado create any directory
(you could remove the dependencies on v$parameter and v$thread but you would have to supply the alert log name to this routine) you could use a setup like the following:
drop table alert_log;create global temporary table alert_log( line int primary key,text varchar2(4000))on commit preserve rows/
create or replace procedure load_alertasl_background_dump_dest v$parameter.value%type;l_filename varchar2(255);l_bfile bfile;l_last number;l_current number;l_start number := dbms_utility.get_time;beginselect a.value, 'alert_' b.instance '.log'into l_background_dump_dest, l_filenamefrom v$parameter a, v$thread bwhere a.name = 'background_dump_dest';execute immediate'create or replace directory x$alert_log$x as''' l_background_dump_dest '''';dbms_output.put_line( l_background_dump_dest );dbms_output.put_line( l_filename );delete from alert_log;l_bfile := bfilename( 'X$ALERT_LOG$X', l_filename );dbms_lob.fileopen( l_bfile );l_last := 1;for l_line in 1 .. 50000loopdbms_application_info.set_client_info( l_line ', ' to_char(round((dbms_utility.get_time-l_start)/100, 2 ) ) ', 'to_char((dbms_utility.get_time-l_start)/l_line));l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );exit when (nvl(l_current,0) = 0);insert into alert_log( line, text )values( l_line, utl_raw.cast_to_varchar2( dbms_lob.substr( l_bfile, l_current-l_last+1, l_last ) ));l_last := l_current+1;end loop;dbms_lob.fileclose(l_bfile);end;/
It'll use a bfile to load up a temporary table with each line of your alert log. On my particular system, this loaded up 50,000 lines in about 1.5 minutes so its not the speediest but it works.
Enhancements:
- this should be a package. the package would remember where I left off bytewise so that I could call a refresh that would load just NEW lines instead of the entire file again. That way, if I kept a session open for a while -- i could load it once and just refresh during the day.
- this should let me pass in some percentage of the file to load -- eg: load the last 10% of the alert log. That would make this apparently faster as well. you would just use dbms_lob.getlength to figure out what 10% of the file represents and start at that offset (l_last := 1 at the top of the loop would be changed).