Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: when Materialized view become stale

    Hi,

    I created a Materialized view (MV), while creating i was not given anything related to stale. I set MV to run every day. After some days i have seen the last time MV refreshed which is 10 days back and status is stale since 10 days.

    Why oracle made MV to stale how can we overcome that the MV never become stale when we set it to run every day.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Since I don't see the DDL for the materialized view I don't know what was specified for all the keywords.

    You can specify multiple values for the 'refresh' keyword:
    REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>

    Check out: Oracle Materialized Views Fast Refresh Complete Log

    You can also setup a refresh group and have the materialized view(s) automatically refreshed when you choose, for example:

    Code:
    declare
      v_date          date := to_date( '2039-12-31-23.59.59', 'yyyy-mm-dd-hh24.mi.ss' );
    begin
      -- Create refresh group
      dbms_refresh.make(
        name                 => 'plans.mv_plan_app_refg',
        list                 => '', 
        next_date            => v_date, 
        interval             => 'sysdate + 1/24',
        implicit_destroy     => false, 
        rollback_seg         => '',
        push_deferred_rpc    => false, 
        refresh_after_errors => false );
    
      -- Add materialized view to refresh group
      dbms_refresh.add(
        name      => 'plans.mv_plan_app_refg',
        list      => 'plans.mv_plan_app_text',
        lax       => true );
    
      -- Add materialized view to refresh group
      dbms_refresh.add(
        name      => 'plans.mv_plan_app_refg',
        list      => 'plans.mv_plan_app_com',
        lax       => true );
    
    end;

    hth

  3. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    i specified refresh every for day for materialized view. like the below

    REFRESH FORCE ON DEMAND START WITH sysdate NEXT (SYSDATE + 1)

    my basic doubt is why it becomes stale after some days and it is not refreshing every day know.

    I am unable to understand refresh group and it's need for materialized view.

  4. #4
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    any one has idea why it is happen...?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by scharan07 View Post
    any one has idea why it is happen...?
    Did you try reading the manual, by chance?

    ON DEMAND Clause

    Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have you set up a job to do the refresh?

  7. #7
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    Have you set up a job to do the refresh?
    No.
    i am doing an R&D, i came across to MV solution.


    For my MV i configured as REFRESH START WITH SYSDATE NEXT (SYSDATE + 1)

    by default the oracle has taken "on DEMAND".
    so this is daily refreshed for some days and after it becomes stale.
    can you tell if any change for the refresh type which does not make MV to stale
    and refresh daily.

Tags for this Thread

Posting Permissions

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