Re: External Tables - Help Please

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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
>

Other related posts: