Need some suggestions with the following query

Question

I need some suggestions with the following query.

SQL> select value from v$dataguard_stats where name like 'apply lag';

VALUE
----------------------------------------------------------------
+00 00:21:38

where +00 --> increases to 1 if the lag is for more than 24 hours. My objective is to write a script which sends a mail whenever the lag is for more than 5 hours i.e whenever the value is greater than +00 05:00:00. The datatype for Value is varchar2(64). I need to rewrite the query in such a way that the query displays output only when the value is greater than or equal to +00 05:00:00. I don't know how to compare this value. Any help/suggestions is highly appreciated.

Please find the description of the view v$dataguard_stats.

SQL> desc v$dataguard_stats;
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 NAME                                                                                               VARCHAR2(32)
 VALUE                                                                                              VARCHAR2(64)
 UNIT                                                                                               VARCHAR2(30)
 TIME_COMPUTED                                                                                      VARCHAR2(30)

Thanks and Regards

Karthik M

Answer 1

The value is stored as a character representation of an INTERVAL value. You can see this by looking at the UNIT column in the v$dataguard_stats view:

SQL> SELECT name, value, unit
  2    FROM v$dataguard_stats
  3   WHERE name = 'apply lag';

NAME            VALUE           UNIT
--------------- --------------- ------------------------------
apply lag       +00 00:00:21    day(2) to second(0) interval

SQL>

This should get you close to what you want:

SELECT 1
  FROM v$dataguard_stats
 WHERE name LIKE 'apply lag'
   AND TO_DSINTERVAL(value) > NUMTODSINTERVAL(5,'HOUR');

This returns 1 if the apply_lag is greater than 5 hours.