Materialized view refresh does not work [message #629340] |
Mon, 08 December 2014 02:01 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have the following materialized view that does not refresh according to the NEXT clause.
In fact it never refreshed since it was created more than one week ago.
CREATE MATERIALIZED VIEW MV_MY_MVIEW
TABLESPACE MYSCHEME
INITRANS 2
STORAGE (
INITIAL 64K
NEXT 1M
MAXEXTENTS UNLIMITED
)
LOGGING
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('24/11/2014 01:00:00', 'MM/DD/SYYYY HH24:MI:SS') NEXT SYSDATE + 1
WITH PRIMARY KEY
USING DEFAULT MASTER ROLLBACK SEGMENT DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS
SELECT *
FROM V_MYVIEW@MY_DBLINK;
Appreciate your support,
Ferro
|
|
|
|
|
|
|
Re: Materialized view refresh does not work [message #629400 is a reply to message #629399] |
Mon, 08 December 2014 08:39 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
you can restart it using
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN
dbms_snapshot.refresh(''MV_MY_MVIEW'',''C'',atomic_refresh => TRUE);
END;'
,next_date => to_date('08/12/2014 01:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'trunc(SYSDATE+1)+1/24 '
,no_parse => FALSE
);
COMMIT;
END;
/
[Updated on: Tue, 09 December 2014 07:03] Report message to a moderator
|
|
|
Re: Materialized view refresh does not work [message #629418 is a reply to message #629400] |
Tue, 09 December 2014 00:49 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi Michel,
Quote:
Those about job execution and mview refresh.
Nothing really! When I try the same mview on testing environment it works fine, also the select statement works fine on both environments! I will recreate it on production and see.
Dear Bill B,
Thanks for your contribution.
Ferro
|
|
|