Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

PoorbandTonyNov 2 2021

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
This post has been answered by PoorbandTony on Nov 3 2021
Jump to Answer

Comments

PoorbandTony
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;

Marked as Answer by PoorbandTony · Nov 3 2021
1 - 1

Post Details

Added on Nov 2 2021
#12c, #general-database-discussions
1 comment
145 views