Forum Stats

  • 3,782,315 Users
  • 2,254,637 Discussions
  • 7,880,045 Comments

Discussions

Oracle 12c - Materialized view refresh issue with BST/GMT switchover

Hi,

I hope someone can help.

We currently have an MV that refreshes every minute, using the expression SYSDATE+1/1440. When the system switches over from BST to GMT, the next refresh time will be set to 02:00:03, for example, when it gets to 01:59:03 - but then the clocks go back. This leaves the refresh time at 02:00:03, and won't refresh for another hour.

I tried setting the time instead to SYS_EXTRACT_UTC(SYSTIMESTAMP)+1/1440 - but it looks like the MV just reverts back to SYSDATE+1/1440 in the expression field - so I'm presuming that's due to SQL Developer saying it's invalid syntax.

Does anyone have any recommendations in how to tackle this?

Thanks in advance,

Tony

Tagged:

Best Answer

  • PoorbandTony
    PoorbandTony Member Posts: 5 Red Ribbon
    Accepted Answer

    Nevermind - figured out that it was SQL Developer being it's usual self, and not committing the changes to the view when I updated the expression in the UI.

    Instead ran the following and it's stuck this time:

    ALTER MATERIALIZED VIEW myview  REFRESH NEXT SYS_EXTRACT_UTC(SYSTIMESTAMP)+1/1440;

Answers

  • PoorbandTony
    PoorbandTony Member Posts: 5 Red Ribbon
    Accepted Answer

    Nevermind - figured out that it was SQL Developer being it's usual self, and not committing the changes to the view when I updated the expression in the UI.

    Instead ran the following and it's stuck this time:

    ALTER MATERIALIZED VIEW myview  REFRESH NEXT SYS_EXTRACT_UTC(SYSTIMESTAMP)+1/1440;