Re: Load alert log into a table with sqlldr

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 16 May 2007 08:50:55 -0700
Message-ID: <1179330651.377171@bubbleator.drizzle.com>

Niall Litchfield wrote:
> Scott wrote:

>> Thanks, I have seen those solutions however, he treats each line as a row 
>> and then uses lag and lead to find the date.  However, some entries are more 
>> than a single line which is why that won't work for me.
>>
>>
>> <fitzjarrell_at_cox.net> wrote in message 
>> news:1179262835.015225.3680_at_p77g2000hsh.googlegroups.com...
>>> On May 15, 3:53 pm, "Scott" <nos..._at_nomail.com> wrote:
>>>> Using a 10.2 version of sqlldr does someone know of a way to load an 
>>>> alert
>>>> log into a table?
>>>>
>>>> For example if my table has the definition
>>>>
>>>> log_entry_dt  date
>>>> log_entry       varchar2(4000)
>>>>
>>>> how can I setup a control file to load the alert log into the database
>>>> table?
>>>>
>>>> looking at the various ulcase files didn't provide any ideas of how I 
>>>> could
>>>> potentially do this.
>>>>
>>>> thanks,
>>>> scott
>>> Tom Kyte explains how to make the alert log an external table.  Visit
>>> asktom.oracle.com and search for alert log.
>>>
>>>
>>> David Fitzjarrell
>>>
>>

>
> You could see if
>
> http://www.niall.litchfield.dial.pipex.com/scripts/admin/read_alertlog.sql
>
> which uses a database table
>
> or
>
> http://www.dbazine.com/oracle/or-articles/still1
>
> which uses an external table suit.
>
> I prefer the external table approach myself.

CREATE TABLE log_table (TEXT VARCHAR2(400)) ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY bdump
  ACCESS PARAMETERS (
   RECORDS DELIMITED BY NEWLINE
   NOBADFILE NODISCARDFILE NOLOGFILE
   FIELDS TERMINATED BY '0x0A'
   MISSING FIELD VALUES ARE NULL)
  LOCATION ('alert_orabase.log'))
REJECT LIMIT unlimited;

SELECT * FROM system.alert_log;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed May 16 2007 - 10:50:55 CDT