Hello Tom,
we're trying to use MV with automatic refresh. As a test, I followed following example where materialized view should be refreshed every minute. I checked several times but nothing was refreshed and the next refresh time was set as original time of view creation. I set several session options but nothing seems to work. Are there some other options, session or database wide that need to be set for the automatic refresh to work. Our DBAs have no experience with automatic refresh and could not help me.
Thank you very much in advance,
Stella.
-----------------------
Create table stella (A int);
Create Materialized view stella_mv refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as select * from stella;
select count(*) from stella;
select count(*) from stella_mv;
begin
for i in 1..10 loop
insert into stella values (i+1);
end loop;
end;
/
commit;
select count(*) from stella;
select count(*) from stella_mv;
select to_char(sysdate,'hh:mi') from dual;
select count(*) from stella;
select count(*) from stella_mv;
alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity=enforced;
select owner
, mview_name
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from all_mviews
order by owner, last_refresh
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from all_jobs
where what like '%dbms_refresh.refresh%';
select job, what, last_date, last_sec, this_date, this_sec, next_date, next_sec, failures, broken from user_jobs ;
select * from user_snapshot_refresh_times;
ALTER MATERIALIZED VIEW STELLA_MV ENABLE QUERY REWRITE;
----------------------
Thank you very much Tom! Your suggestion was exactly right. Last night our database developer and I figured out exactly the same thing and everything just worked.
I'm posting here his example, so other people could see it:
Stella,
I create a materialized view test_refresh_mv in my local database after job queue processes parameter was set up as follows:
alter system set job_queue_processes=10 scope=both;
--test_refresh_mv--
create materialized view test_refresh_mv
refresh complete
START WITH systdate
NEXT TO_DATE sysdate+10/1440
WITH PRIMARY KEY
ENABLE QUERY REWRITE
as select * from test_refresh_mv_table
This materialized works just fine and refreshes every 10 minutes, which means that it is like a transactional local table on the condition that
the refresh is completed in 10 minutes.
For verification:
1)
SQL> select count(*) from test_refresh_mv
COUNT(*)
------------------
10
2)
SQL> select owner, mview_name, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh from all_mviews
order by owner, last_refresh 2 3 4 /
OWNER MVIEW_NAME LAST_REFRESH
------------------------------ ------------------------------ --------------
edward &n bsp; test_refresh_mv 24/10/11 21:39
The account I tested only has RESOURCR role and create materialized view privilege as I purposefully created an account with least privileges so that
an account without system, sys dba super user privileges can also invoke auto refresh materialized view procedure.
So the problem we have been facing lies in the job_queue_processes parameter values
Please let me know of any questions or comments.