Home » RDBMS Server » Performance Tuning » Materialized View, which should refresh every Tuesday and Friday at 5am. (merged 2) (Oracle 11.2.0.2.0)
Materialized View, which should refresh every Tuesday and Friday at 5am. (merged 2) [message #548131] Tue, 20 March 2012 08:24 Go to next message
vishnu_ora
Messages: 12
Registered: April 2011
Junior Member

Guys,

Please help me with ...

I need to create a Materialized View, which should refresh every Tuesday and Friday at 5am.

CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;

I can do it for single day like above, but how to refresh on multiple days, using create materialized view syntax ........

Re: Materialized View, which should refresh every Tuesday and Friday at 5am. (merged 2) [message #548139 is a reply to message #548131] Tue, 20 March 2012 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;

CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'FRIDAY') + 05/24
AS SELECT * FROM DUAL;
Re: Materialized View, which should refresh every Tuesday and Friday at 5am. (merged 2) [message #548143 is a reply to message #548131] Tue, 20 March 2012 08:56 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a CASE expression, if current is TUESDAY then use SYSDATE+1+5/24 otherwise use your current expression (CASE WHEN ELSE).

Regards
Michel
Previous Topic: How to find Local and global indexes on Particular Table
Next Topic: New User Help Me Out (merged 2)
Goto Forum:
  


Current Time: Sat Dec 27 11:59:38 CST 2014

Total time taken to generate the page: 0.05825 seconds