Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trouble with Materialized view refresh

418779Nov 12 2008 — edited Feb 25 2009
I 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 25 2009
Added on Nov 12 2008
15 comments
3,088 views