Re: External Tables - Help Please

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 21 Sep 2009 10:20:30 -0700
Message-ID: <bf46380909211020i2e82d80dh3c25b649f7473080_at_mail.gmail.com>

Assuming the table is named alert_log and the column is named al_row with al as (
  select al_row, rownum al_rownum
  from alert_log
)
select al_row
from al
where al_rownum >=
(
  select al_rownum
  from al
  where al_row = ''ALERT LOG TIMESTAMP: ' || to_char(sysdate,'mm/dd/yyyy') )

This query would require the timestamp in the alert log to be for year, month
and day only.

I haven't tested it, you will need to experiment with it.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

On Mon, Sep 21, 2009 at 9:39 AM, BN <bnsarma_at_gmail.com> wrote:

> Greetings Jared
>
> Not sure how I can use this timestamp to return all the rows of Today
>
>
>
> On Mon, Sep 21, 2009 at 12:30 PM, Jared Still <jkstill_at_gmail.com> wrote:
>
>> One method to try is to write a timestamp to the alert log at the
>> beginning of each day. Then just return
>> the rows following the timestamp.
>>
>> eg:
>> begin
>> sys.dbms_system.ksdwrt(2,'ALERT LOG TIMESTAMP: ' ||
>> to_char(sysdate,'mm/dd/yyyy hh24:mi'));
>> end;
>>
>>
>>
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Oracle Blog: http://jkstill.blogspot.com
>> Home Page: http://jaredstill.com
>>
>>
>>
>>
>> On Mon, Sep 21, 2009 at 8:58 AM, BN <bnsarma_at_gmail.com> wrote:
>>
>>> Greetings
>>>
>>> I am using Oracle external tables to read alert log to display the
>>> content on a webpage
>>>
>>> Oracle Version :oracle 9.2.0.4 and oracle10grel2
>>>
>>> Looking for a sql to read Today's alert log contents only,
>>>
>>> Looked at uinsg AWK to create just todays file only, unless I run this
>>> every 2 mins or so, I wont see the latest alertlog info, dont want to go
>>> this route unless
>>> there is no other option
>>>
>>> Here is the SQL right now I am using to read last 100 lines, even this
>>> the Timestamp comes after the messages becase of DESC, not in the right
>>> order
>>>
>>> select rownum,text as "alert_${DB}.log" from
>>> (select alert_log.text, rank() over (order by rownum desc) r from
>>> alert_log)
>>> where r <= 100
>>>
>>> Appreciate your help
>>> --
>>> Regards & Thanks
>>> BN
>>>
>>
>>
>
>
> --
> Regards & Thanks
> BN
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 21 2009 - 12:20:30 CDT