MAT view - Start with & Next [message #636067] |
Wed, 15 April 2015 16:31 |
|
Hi,
I need help on creating a materialized view with below configuration. Can someone please help?
Start with : Immediate
Refresh next time : 2 AM everyday (only once in a day)
Thanks.
|
|
|
|
|
|
|
Re: MAT view - Start with & Next [message #636081 is a reply to message #636077] |
Thu, 16 April 2015 06:32 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Personally, I prefer:
1. Create refresh group
2. Create MV with refresh ON DEMAND
3. Add MV to refresh group
3. Create DBMS_SCHEDULER schedule and job to refresh not MV but refresh group.
4. Create DBMS_SCHEDULER job email notification (if needed) to email support team on failures (or even on success)
Makes it much easier to maintain. You add all related MVs to refresh group and refresh group rather than individual MVs (refresh whole group or nothing).
SY.
|
|
|
Re: MAT view - Start with & Next [message #636101 is a reply to message #636067] |
Thu, 16 April 2015 18:02 |
|
Hi,
I have used the below create statement for MAT view. But it refresh the MAT view for every one hour instead of 1 AM everyday,
CREATE MATERIALIZED VIEW MVW_EXPORT_DATA
BUILD IMMEDIATE
REFRESH COMPLETE
NEXT (sysdate + 1/24)
AS
SELECT *
FROM VW_CAT_SEO;
|
|
|
|
|
Re: MAT view - Start with & Next [message #636121 is a reply to message #636120] |
Fri, 17 April 2015 01:12 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> desc dba_mviews
Name Null? Type
-------------------------------- -------- ----------------------
OWNER NOT NULL VARCHAR2(30)
MVIEW_NAME NOT NULL VARCHAR2(30)
CONTAINER_NAME NOT NULL VARCHAR2(30)
QUERY LONG
QUERY_LEN NUMBER(38)
UPDATABLE VARCHAR2(1)
UPDATE_LOG VARCHAR2(30)
MASTER_ROLLBACK_SEG VARCHAR2(30)
MASTER_LINK VARCHAR2(128)
REWRITE_ENABLED VARCHAR2(1)
REWRITE_CAPABILITY VARCHAR2(9)
REFRESH_MODE VARCHAR2(6)
REFRESH_METHOD VARCHAR2(8)
BUILD_MODE VARCHAR2(9)
FAST_REFRESHABLE VARCHAR2(18)
LAST_REFRESH_TYPE VARCHAR2(8)
LAST_REFRESH_DATE DATE
STALENESS VARCHAR2(19)
AFTER_FAST_REFRESH VARCHAR2(19)
UNKNOWN_PREBUILT VARCHAR2(1)
UNKNOWN_PLSQL_FUNC VARCHAR2(1)
UNKNOWN_EXTERNAL_TABLE VARCHAR2(1)
UNKNOWN_CONSIDER_FRESH VARCHAR2(1)
UNKNOWN_IMPORT VARCHAR2(1)
UNKNOWN_TRUSTED_FD VARCHAR2(1)
COMPILE_STATE VARCHAR2(19)
USE_NO_INDEX VARCHAR2(1)
STALE_SINCE DATE
NUM_PCT_TABLES NUMBER
NUM_FRESH_PCT_REGIONS NUMBER
NUM_STALE_PCT_REGIONS NUMBER
See the date column.
|
|
|