Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: Materialized View ...

    Hi ...

    I have an problem. I am working with Oracle 10.2.0.2, and I am starting to work with Materialized Views.
    In the Syntax I am using, I am giving the command FAST REFRESH with and automatic update of the MV for instance, by 15 minutes interval.

    The problem is that, that automatic refresh does not occur ...

    Any ideas ???

    Thanx

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Did you create a MATERIALIZED VIEW LOG? ... And followed these requirements?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    Yes ...

    I have created the Materialized View Log, on the source table, and have respected all those restrictions, but the problem is that the automatic refresh doesnt happen, automatically.

    Is there any general Database setting that manages that ??

    Manually refresh does not give any problem and works fine ... only automatic refresh is not working.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    look, if you provide your dang code then maybe we can help.

    "help ME to help YOU!"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Have you created the Replication Environment?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Mar 2011
    Posts
    2

    helpp

    Quote Originally Posted by aucrun View Post
    Yes ...

    I have created the Materialized View Log, on the source table, and have respected all those restrictions, but the problem is that the automatic refresh doesnt happen, automatically.

    Is there any general Database setting that manages that ??

    Manually refresh does not give any problem and works fine ... only automatic refresh is not working.
    Based on my project, I need to update the materialized join view.
    I am using join queries.even if update query on join is executed, materialized join view is not updated. I want the update to be reflected on materialised join view, soon after the update query on any table constituting the join view is issued.

    Sir, Is there any option for this.I tried, but did not work like that.Is there any settings which we should modify in order to do so.

    First did like below
    create table T1(aa number,bb number)
    create table T2(bb number, name varchar2)
    insert into T2 values(1,a)
    insert into T2 values(2,b);
    insert into T2 values(3,c);
    insert into T1 values(1,100)
    insert into T1 values(2,200);
    insert into T1 values(3,300);

    then view is created.
    CREATE MATERIALIZED VIEW cust_sales_mvnew
    PARALLEL
    BUILD IMMEDIATE
    REFRESH COMPLETE
    AS
    Select T1.aa, T1.bb, T2.name from mm1 T1, mm2 T2 where T1.aa=T2.bb

    update T2 set name='hem' where bb=3
    then the table T2 is updated, not the view. when query rewrite is enabled true and the command is executed error is shown.

    ORA-00439: feature not enabled: Materialized view rewrite

    sir, even if it was set to true, the same error is showing..
    Sir, can you please help me.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    have you turned on the database job submitting. Automatic refreshes are done by scheduled jobs.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool To REWRITE or not to REWRITE...That is the question!

    Have you enabled query re-write?
    Code:
    ALTER MATERIALIZED VIEW cust_sales_mvnew ENABLE QUERY REWRITE;
    Also perhaps you need to grant the 'QUERY REWRITE' privilege to the source and target accounts (users).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I just noticed your ORA-00439. What version of the database are you using

    select * from v$version;

    What that indicates is that you do not have that feature in the database and your out of luck.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •