Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view refresh does not work (11.2.0.1.0)
Materialized view refresh does not work [message #629340] Mon, 08 December 2014 02:01 Go to next message
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 #629361 is a reply to message #629340] Mon, 08 December 2014 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the value of "job_queue_processes" parameter?
Nothing in the alert.log file?

Re: Materialized view refresh does not work [message #629370 is a reply to message #629361] Mon, 08 December 2014 04:02 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Michel,

 select value from v$parameter where name='job_queue_processes';

Value 
50


Quote:

Nothing in the alert.log file?


Seems nothing, is there a specific alert name I should be looking for?

Thanks,
Ferro
Re: Materialized view refresh does not work [message #629371 is a reply to message #629370] Mon, 08 December 2014 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Those about job execution and mview refresh.

Re: Materialized view refresh does not work [message #629399 is a reply to message #629371] Mon, 08 December 2014 08:33 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The refresh is done using a dba_job that is scheduled to run every interval that you choose when the MVIEW was created. Also unless you want refresh creep use

NEXT trunc(SYSDATE + 1) + 1/24

to refresh everyday at 1am.

[Updated on: Mon, 08 December 2014 08:35]

Report message to a moderator

Re: Materialized view refresh does not work [message #629400 is a reply to message #629399] Mon, 08 December 2014 08:39 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Perhaps a stupid query
Next Topic: dumping data from *.rtf file to oracle table
Goto Forum:
  


Current Time: Thu Apr 25 22:19:03 CDT 2024