Ah, well. I forgot to explain that bit. You create a matierialized view on
the externally-organized table. You index the view. Not sure the MV will
ever fast refresh, but you can schedule the complete at least. 'Course: you
can parallelize the read of the original alert log in the first place, too.
Silly me.
HJR
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:HKUI8.20035$b5.69734_at_newsfeeds.bigpond.com...
> Good idea !
>
> Except, I'm sure about adding the index on the column. This is not
supported
> with external tables (as it's impossible for Oracle to create and
maintain).
>
> Regards
>
> Richard
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:ad0sgt$ak3$1_at_lust.ihug.co.nz...
> > For what it's worth: in 9i it's possible to attach external text files
as
> > pseudo-tables ('create table blah.... organization external').
Therefore,
> > just as a thought, you create an external table comprising a single,
> > un-differentiated, column that references each database's alert log. You
> > slap a text index on the column. Voilą! You can now search for ORA-600's
> and
> > any other nasties using standard select statements. Create a database
link
> > or two, and you can monitor other databases' alert logs from the one
> > location.
> >
> > Advantages of this solution: it works whether you run Unix or something
> > else. And it requires no shell scripting skills, just the nouse to type
in
> a
> > complex create table statement.
> >
> > Regards
> > HJR
> >
> >
> > "Steve Perry" <sperry_at_sprynet.com> wrote in message
> > news:actk54$87r$1_at_slb5.atl.mindspring.net...
> > > I thought this might be useful to somebody. I wrote it (with help from
> > > metalink and akadia) because I hated having to telnet to different
> servers
> > > to look at the alert log. save it off in directory, start sqlplus and
> > type
> > > @alertlog. It will show you filtered errors in it. If you don't manage
> the
> > > alertlogs and archive them weekly or monthly, this can take a bit to
> run.
> > On
> > > my systems, it takes a few seconds. I'm almost done converting it to a
> > > package with more features (i.e. date range, you don't have keep
> creating
> > > the directory). It does require v8.0+ and the utl_raw package. Other
> than
> > > that, it should work just fine.
> > >
> > > If you see areas for improvement, let me know.
> > >
> > > Steve
> > >
> > > rem clear screen
> > > set echo off
> > > set lines 150
> > > set arraysize 120
> > >
> > > --
> > >
> * ------------------------------------------------------------------------
> > *
> > > -- * @alertlog
> > > *
> > > -- * FILE: alertlog.sql
> > > *
> > > -- * SOURCE: "glued" together code from metalink, akadia and my own
> > > *
> > > -- * AUTHOR: Steve Perry and above mentioned caveats.
> > > *
> > > -- * EMAIL : sperry_at_sprynet.com
> > > *
> > > -- * DATE : 3/15/02
> > > *
> > > -- * INPUT : none
> > > *
> > > -- * OUTPUT: report
> > > *
> > > -- * ORACLE: 8.0.x and above.
> > > *
> > > -- * Restrictions: assumes the path is "hardcoded" and no env vars.
> > > *
> > > -- * DESCRIPTION: displays info about errors and things of interest in
> the
> > > *
> > > -- * database alert log.
> > > *
> > > -- *
> > > *
> > > -- * Maintenance
> > > *
> > > -- * SMP 3/15/02 Created, *
> > > -- * smp 3/18/02 modified for Unix support and alertlog
> > > default*
> > > -- * names: Unix : alert_<sid>.log *
> > > -- * Windows: <sid>ALRT.LOG *
> > > -- *
> > > *
> > > --
> > >
> * ------------------------------------------------------------------------
> > *
> > >
> > > set verify off
> > > set feedback off
> > >
> > > --Determine the OS (WIN or UNIX)
> > > col OS_PLATFORM new_value OS_PLATFORM noprint
> > > select decode(instr(upper(product), 'WINDOWS',1), 0 , 'UNIX',
'WINDOWS')
> > > OS_PLATFORM
> > > from PRODUCT_COMPONENT_VERSION
> > > WHERE upper(PRODUCT) LIKE 'TNS%'
> > > /
> > >
> > > col dirname new_value dirname noprint
> > > select decode('&OS_PLATFORM', 'WINDOWS', value || '\', value || '/')
> > dirname
> > > from v$parameter
> > > where name = 'background_dump_dest'
> > > /
> > >
> > > -- debug stuff
> > > -- prompt dir = &&dirname
> > >
> > > create or replace directory "ALERTDIR" as '&&dirname';
> > >
> > > --allows leading whitespace to be printed by dbms_output
> > > SET SERVEROUTPUT ON FORMAT WRAPPED
> > >
> > > rem clear screen
> > >
> > > -- ****************************************************************
> > > -- BEGIN CODE PART
> > > -- ****************************************************************
> > >
> > > DECLARE
> > > -- Input Directory as specified in create directory
> > > l_dir CONSTANT VARCHAR2(30) := 'ALERTDIR';
> > >
> > > -- Input File which is read word by word
> > > l_fil VARCHAR2(30) ;
> > > l_fil_size number;
> > >
> > > -- Separator Character between words is a BLANK (ascii = 32)
> > > l_sep CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(32));
> > > l_eol CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(10));
> > >
> > > -- Character at the end of the file is NEWLINE (ascii = 10)
> > > l_sen CONSTANT RAW(500) := UTL_RAW.CAST_TO_RAW(CHR(10));
> > >
> > > -- Pointer to the BFILE
> > > l_loc BFILE;
> > > --l_mode_RO BINARY_INTEGER := file_readonly;
> > >
> > > -- Current position in the file (file begins at position 1)
> > > l_pos NUMBER := 1;
> > >
> > > -- Amount of characters have been read
> > > l_read_amt BINARY_INTEGER := 0;
> > >
> > > -- Read BufferS
> > > l_rawbuf raw(500);
> > > l_buf VARCHAR2(500);
> > >
> > > -- End of the current line which will be read
> > > l_end NUMBER;
> > >
> > > -- Return value
> > > l_ret BOOLEAN := FALSE;
> > >
> > > -- DB Name to resolve alert log
> > > l_ora_sid varchar2(30);
> > >
> > > -- work date var
> > > l_date date;
> > >
> > > --alert string
> > > l_alrt varchar2(10);
> > >
> > > --debug setting
> > > bDebug boolean := FALSE;
> > >
> > > --OS PLATFORM
> > > l_OS_PLATFORM varchar2(15) := 'WINDOWS';
> > >
> > > BEGIN
> > > -- set the max buffer size
> > > dbms_output.enable(1000000);
> > >
> > > --get database name
> > > select value into l_ora_sid from sys.v_$parameter where name =
> > > 'db_name';
> > >
> > > --Determine the OS (WIN or UNIX)
> > > select decode(instr(upper(product), 'WINDOWS',1), 0 , 'UNIX',
> > 'WINDOWS')
> > > into l_OS_PLATFORM
> > > from sys.PRODUCT_COMPONENT_VERSION
> > > WHERE upper(PRODUCT) LIKE 'TNS%';
> > >
> > > --set the alert file name now
> > > if l_OS_PLATFORM = 'WINDOWS' then
> > > l_fil := l_ora_sid || 'ALRT.LOG';
> > > else
> > > l_fil := 'alert_' || l_ora_sid || '.log';
> > > end if;
> > >
> > > -- Mapping the physical file with the pointer to the BFILE
> > > l_loc := BFILENAME(l_dir,l_fil);
> > >
> > > dbms_output.put_line('File: ALERTLOG.SQL - Compliments of
SQL*Buddy
> > > (Steve Perry)...' );
> > > dbms_output.put_line('Date: ' || to_char(sysdate, 'DY, MON DD
> > HH24:MI:SS
> > > YYYY') );
> > > dbms_output.put_line('Processing alert.log for sid: ' ||
> l_ora_sid );
> > > dbms_output.put_line('Opening File ' || l_fil || ' in Directory '
||
> > > l_dir );
> > >
> > > -- Check if the file exists
> > > l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1;
> > > IF (l_ret) THEN
> > > -- Open the file in READ_ONLY mode
> > > -- DBMS_LOB.OPEN(l_loc, DBMS_LOB.LOB_READONLY);
> > > DBMS_LOB.FILEOPEN(l_loc, DBMS_LOB.file_readonly );
> > >
> > > l_fil_size := DBMS_LOB.GETLENGTH( l_loc );
> > > dbms_output.put_line('Alert log size is: ' ||
> > > ltrim(to_char(l_fil_size, '999,999,999,999,999')) || ' bytes.' ||
> > chr(10));
> > >
> > > --print heading
> > > dbms_output.put_line(chr(10) || 'Date
> > > Description');
> > >
> > >
> > >
> >
tput.put_line('------------------------- --------------------------
> > > --------------------------------------');
> > >
> > > /**************/
> > > LOOP
> > > /*************/
> > > -- find the end of the current line
> > > l_end := DBMS_LOB.INSTR(l_loc,l_eol,l_pos,1);
> > > -- Process end-of-file
> > > --only goes into here if: reached end of file l_end = 0
> > > IF (l_end = 0) THEN
> > > l_end := DBMS_LOB.INSTR(l_loc,l_eol,l_pos,1);
> > > l_read_amt := l_end - l_pos - 1;
> > > --debug stuff
> > > IF bDebug then
> > > dbms_output.put_line(' --------------- ' );
> > > dbms_output.put_line('l_fil_size: ' ||
> to_char(l_fil_size,
> > > '999,999,999,999') );
> > > dbms_output.put_line('l_end: ' || to_char(l_end ,
> > > '999,999,999,999'));
> > > dbms_output.put_line('l_read_amt: ' ||
to_char(l_read_amt
> ,
> > > '999,999,999,999'));
> > > dbms_output.put_line('l_pos: ' || to_char(l_pos ,
> > > '999,999,999,999'));
> > > dbms_output.put_line('l_rawbuf: ' ||
> > > UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf) );
> > > dbms_output.put_line(' --------------- ' );
> > > --debug stuff
> > > -- DBMS_LOB.READ(l_loc,l_read_amt,l_pos,l_rawbuf);
> > > -- l_buf := UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf);
> > > -- dbms_output.put_line(l_buf);
> > > EXIT;
> > > END IF;
> > > END IF;
> > >
> > > --set chunk of data to read
> > > l_read_amt := l_end - l_pos;
> > > -- Read until end-of-file
> > > DBMS_LOB.READ(l_loc,l_read_amt,l_pos,l_rawbuf);
> > >
> > > --debug stuff
> > > -- if bDebug then
> > > -- dbms_output.put_line(' --------------- ' );
> > > -- dbms_output.put_line('l_end: ' || to_char(l_end ,
> > > '999,999,999,999'));
> > > -- dbms_output.put_line('l_read_amt: ' ||
to_char(l_read_amt
> ,
> > > '999,999,999,999'));
> > > -- dbms_output.put_line('l_pos: ' || to_char(l_pos ,
> > > '999,999,999,999'));
> > > -- dbms_output.put_line(' --------------- ' );
> > > -- --debug stuff
> > > -- end if;
> > >
> > > --convert the line to character data
> > > l_buf := UTL_RAW.CAST_TO_VARCHAR2(l_rawbuf);
> > > --if win os, strip off the chr(12), otherwise leave it
alone.
> > > if l_OS_PLATFORM = 'WINDOWS' then
> > > l_buf := substr(l_buf, 1, length(l_buf) - 1);
> > > end if;
> > >
> > >
> > > --hack to find the date (line prior to error)
> > > if substr(l_buf, 4,1) = ' ' then
> > > begin
> > > -------select to_date('Tue Mar 12 03:24:18 2002', 'DY,
> MON
> > > DD HH24:MI:SS YYYY') from dual
> > > select to_date(l_buf, 'DY, MON DD HH24:MI:SS YYYY')
into
> > > l_date from dual;
> > > exception
> > > when others then
> > > l_date := null;
> > > end;
> > > end if;
> > > IF instr(l_buf,'ORA-' , 1 ) > 0
> > > or instr(l_buf,'Starting up ORACLE' , 1 ) > 0
> > > or instr(l_buf,'Shutting down instance', 1 ) > 0
> > > or instr(l_buf,'Errors in' , 1 ) > 0
> > > or instr(l_buf,'alter database ' , 1 ) > 0
> > > or instr(l_buf,'Completed: alter data' , 1 ) > 0 then
> > > --hilight errors...
> > > if instr(l_buf, 'ORA-', 1) > 0 then
> > > l_buf := ' !! ' || ltrim(l_buf);
> > > l_alrt := chr(10);
> > > else
> > > l_buf := ' ' || ltrim(l_buf);
> > > l_alrt := '';
> > > end if;
> > > dbms_output.put_line(l_alrt || to_char(l_date, 'DY,
MON
> DD
> > > HH24:MI:SS YYYY') || l_buf);
> > > -- dbms_output.put_line(' - ' || l_buf);
> > > END IF;
> > > --move pointer to next part of data
> > > l_pos := l_pos + l_read_amt + 1;
> > > IF l_pos > l_fil_size THEN
> > > exit;
> > > END IF;
> > > /***********/
> > > END LOOP;
> > > /***********/
> > > --close the file normally
> > > DBMS_LOB.FILECLOSE(l_loc);
> > > dbms_output.put_line( chr(10) || chr(10) );
> > > ELSE
> > > dbms_output.put_line(chr(10) || 'File ' || l_fil || ' in
> Directory
> > '
> > > || l_dir || ' was NOT FOUND' || chr(10));
> > > END IF;
> > >
> > > EXCEPTION
> > > WHEN OTHERS THEN
> > > dbms_output.put_line('Error: ' || SQLERRM);
> > > DBMS_LOB.FILECLOSE(l_loc);
> > > END;
> > > /
> > >
> > >
> > > drop directory "ALERTDIR"
> > > /
> > >
> > > set verify off
> > > set feedback on
> > > set echo on
> > >
> > >
> > >
> >
> >
>
>
Received on Tue May 28 2002 - 19:13:06 CDT
