Re: REGEXP

From: oracledba <oracledba71_at_gmail.com>
Date: Fri, 13 Sep 2013 21:37:57 -0400
Message-ID: <CA+eRr5Hb3npMtekVEPFyzw-GUBPdXBGdrVbanTwLu0zzhnBgBA_at_mail.gmail.com>

Hi Andy,
The monitoring tool has a limitation to accept only either OK or Critical string.So I am using a count and then sending the output as OK when count is 0 or Critical when count is greater than 0.

select count(*) from dba_jobs
where next_date < get_interval(REPLACE(UPPER(INTERVAL), 'SYSDATE','LAST_DATE')); where next_date < get_interval(REPLACE(UPPER(INTERVAL), 'SYSDATE','LAST_DATE'))

                  *

ERROR at line 2:

ORA-06550: line 1, column 18:
PLS-00201: identifier 'LAST_DATE' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored
ORA-06512: at "SYS.GET_INTERVAL", line 6

thanks for sharing.

On Fri, Sep 13, 2013 at 5:58 PM, Andy Klock <andy_at_oracledepot.com> wrote:

> Yeah, it's rather annoying that the "interval" is stored as a varchar.
> Potentially, you could get the date INTERVAL is computing by executing it
> dynamically in a function and then use that to verify if the times between
> the last_date and next_date have been exceeded.
>
> for example:
>
> create or replace function get_interval (p_interval in varchar2)
> return varchar2
> is
> return_value varchar2(128);
> begin
> execute immediate 'begin :result := ' || p_interval || '; end;'
> using out return_value;
> return return_value;
> end;
> /
>
> SQL> select get_interval('TRUNC(SYSDATE + 1) + 2.75/24') from dual;
>
> GET_INTERVAL('TRUNC(SYSDATE+1)+2.75/24')
>
> --------------------------------------------------------------------------------
> 2013-SEP-14 02:45:00
>
> >>> no matter how complex~
>
> SQL> select get_interval('NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3),
> ''FRIDAY'')') from dual;
>
> GET_INTERVAL('NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,''Q''),3),''THURSDAY'')')
>
> --------------------------------------------------------------------------------
> 2013-OCT-04 00:00:00
>
> Then you could do Sayan's trick with replace:
>
> select job, what from dba_jobs
> where next_date < get_interval(REPLACE(UPPER('TRUNC(SYSDATE + 1) +
> 2.75/24'), 'SYSDATE','LAST_DATE'));
>
> Though, as I think about this that method has lots of holes in it (not to
> mention a context switch for every row filtered...)
>
> You mentioned you didn't want to use DBA_JOBS.BROKEN, but what about
> DBA_JOBS.FAILURES? FAILURES should be 0 for your refreshes so if it's not,
> then is it safe to assume that the refresh failed?
>
> Or alternatively, there is a view called *_MVIEW_ANALYSIS that you can use
> to see when the refresh was started, if it is still refreshing, and how
> long it took to complete (using FULLREFRESHTIM for complete refreshes and
> INCREFRESHTIM for fast refreshes, in seconds).
>
> http://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_1141.htm
>
>
>
>
> On Fri, Sep 13, 2013 at 9:54 AM, oracledba <oracledba71_at_gmail.com> wrote:
>
>> Actually i am trying to list out materialized views which are not
>> refreshed
>> based on its interval and next_date and last_date.Since MVs are refreshed
>> at different intervals i need to report when MV refresh is failed.
>> I don't want to use "broken" column for it.Interval in dba_jobs is a
>> varchar column.
>>
>>

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

Received on Sat Sep 14 2013 - 03:37:57 CEST