Trouble with Materialized view refresh
418779Nov 12 2008 — edited Feb 25 2009I am using oracle 9 and i issue the following command through oracle enterprise manager to create a new materialized view:
CREATE MATERIALIZED VIEW "AZTECA"."MV_PETROVEND"
BUILD DEFERRED
REFRESH COMPLETE
START WITH to_date('12-Nov-2008 05:23:51 PM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 10/1440
AS
SELECT 'TransNum','Card2','Odometer' FROM transactions@PETROVEND.WORLD WHERE 'Odometer' IS NOT NULL
I do this through the create 'wizard' on the materialized view tab - so this SQL is what the dialog is generating and showing me in the "Show SQL" box.
After this runs I get a materialized view alright but not the one i requested. If I use Aqua data studio and script out the mv I get the following:
CREATE MATERIALIZED VIEW AZTECA.MV_PETROVEND
BUILD DEFERRED
REFRESH ON DEMAND
DISABLE QUERY REWRITE
AS
SELECT 'TransNum','Card2','Odometer' FROM transactions@PETROVEND.WORLD WHERE 'Odometer' IS NOT NULL
What is going on here? This is an mv i am creating on a database link to an access table. I need for it to update itself every 10 minutes.
It's not as if I'm creating bad SQL in my initial request, i'm letting ent manager do that for me. So why can't i have a timed refresh?