Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: refreshing materialized view

    Hi All,
    Very often I could see the below list of error messages in the alert.log w.r.t a materialized view.
    What could be the problem and how to rectify it?

    Error Message:
    Wed Jan 10 10:26:56 2007
    ...skipping...
    ORA-12008: error in materialized view refresh path
    ORA-08103: object no longer exists
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
    ORA-06512: at "SYS.DBMS_IREFRESH", line 683
    ORA-06512: at "SYS.DBMS_REFRESH", line 195
    ORA-06512: at line 1
    Wed Jan 10 15:53:26 2007

    I do not get the error when I try to refresh the same materialized view using
    SQL> execute dbms_refresh.refresh('"DEVELOPMENT"."DEV_BLUE_BOOK _YIELD_REP_MV"');

    Please help.

    Thanks in advance
    qA

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    why is there a blank space in the object name?

    provide the MV code
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    70

    MV code

    Hi,
    Thanks for the reply. The blank space was a typo error (no blank space exists)

    Code for MV is:

    CREATE MATERIALIZED VIEW DEV_BLUE_BOOK_YIELD_REP_MV
    TABLESPACE DEVELOPMENT
    NOCACHE
    LOGGING
    NOPARALLEL
    REFRESH FORCE
    START WITH TO_DATE('16-Jan-2007 15:54:27','dd-mon-yyyy hh24:mi:ss')
    NEXT SYSDATE + 12/24
    AS
    SELECT * FROM DEV_BLUE_BOOK_YIELD_REP_V;


    MV fetches data from a view:

    Code of view:

    CREATE OR REPLACE VIEW DEV_BLUE_BOOK_YIELD_REP_V
    AS
    SELECT E.FULL_NAME, E.ATTRIBUTE6 AS EMPLOYEE_NUMBER,
    E.EMPLOYEE_TYPE, E.SUPERVISOR_NAME, E.OBU_NAME, E.CENTER_NAME,
    E.PROGRAM_NAME, E.LOCATION, E.MODE_OF_DELIVERY, E.ROLE, E.BAND, E.ATTRIBUTE7,
    E.ATTRIBUTE8, E.ATTRIBUTE9,
    (SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
    FROM APPS.YIELD_CALC
    WHERE
    YIELD_CALC.START_DATE>'01-OCT-2006' AND
    E.PERSON_ID=YIELD_CALC.PERSON_ID
    AND START_DATE=
    CASE TO_CHAR(SYSDATE, 'D')
    WHEN '1' THEN TRUNC(SYSDATE)-13
    WHEN '2' THEN TRUNC(SYSDATE)-7
    WHEN '3' THEN TRUNC(SYSDATE)-8
    WHEN '4' THEN TRUNC(SYSDATE)-9
    WHEN '5' THEN TRUNC(SYSDATE)-10
    WHEN '6' THEN TRUNC(SYSDATE)-11
    WHEN '7' THEN TRUNC(SYSDATE)-12
    END
    ) AS PREV_YIELD,
    (SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
    FROM APPS.YIELD_CALC
    WHERE E.PERSON_ID=YIELD_CALC.PERSON_ID
    AND START_DATE BETWEEN
    CASE TO_CHAR(SYSDATE, 'D')
    WHEN '1' THEN TRUNC(SYSDATE)-34
    WHEN '2' THEN TRUNC(SYSDATE)-28
    WHEN '3' THEN TRUNC(SYSDATE)-29
    WHEN '4' THEN TRUNC(SYSDATE)-30
    WHEN '5' THEN TRUNC(SYSDATE)-31
    WHEN '6' THEN TRUNC(SYSDATE)-32
    WHEN '7' THEN TRUNC(SYSDATE)-33
    END
    AND
    CASE TO_CHAR(SYSDATE, 'D')
    WHEN '1' THEN TRUNC(SYSDATE)-13
    WHEN '2' THEN TRUNC(SYSDATE)-7
    WHEN '3' THEN TRUNC(SYSDATE)-8
    WHEN '4' THEN TRUNC(SYSDATE)-9
    WHEN '5' THEN TRUNC(SYSDATE)-10
    WHEN '6' THEN TRUNC(SYSDATE)-11
    WHEN '7' THEN TRUNC(SYSDATE)-12
    END
    ) AS PREV_3WEEK_YIELD,
    (SELECT NVL(SUM(HOURS_WORKED)/DECODE((SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)),0,1,(SUM(HOURS_PER_WEEK)-SUM(LEAVE_HOURS)))*100,0)
    FROM APPS.YIELD_CALC
    WHERE E.PERSON_ID=YIELD_CALC.PERSON_ID
    AND START_DATE BETWEEN
    CASE TO_CHAR(SYSDATE, 'D')
    WHEN '1' THEN TRUNC(SYSDATE)-97
    WHEN '2' THEN TRUNC(SYSDATE)-91
    WHEN '3' THEN TRUNC(SYSDATE)-92
    WHEN '4' THEN TRUNC(SYSDATE)-93
    WHEN '5' THEN TRUNC(SYSDATE)-94
    WHEN '6' THEN TRUNC(SYSDATE)-95
    WHEN '7' THEN TRUNC(SYSDATE)-96
    END
    AND
    CASE TO_CHAR(SYSDATE, 'D')
    WHEN '1' THEN TRUNC(SYSDATE)-13
    WHEN '2' THEN TRUNC(SYSDATE)-7
    WHEN '3' THEN TRUNC(SYSDATE)-8
    WHEN '4' THEN TRUNC(SYSDATE)-9
    WHEN '5' THEN TRUNC(SYSDATE)-10
    WHEN '6' THEN TRUNC(SYSDATE)-11
    WHEN '7' THEN TRUNC(SYSDATE)-12
    END
    ) AS PREV_13WEEK_YIELD
    FROM
    DEVELOPMENT.DEV_BLUE_BOOK E;

    ----------

    DEV_BLUE_BOOK referred in the view code is another materialized view which refreshes every one hour.

    Thanks
    Anand

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    views based on views based on other views?

    make sure you do not have overlapping refresh times.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    70
    Yea that could be the reason. Is it not a good idea to have materialized view referencing another materialized view?
    Can you share your thoughts on that?

  6. #6
    Join Date
    Aug 2006
    Posts
    8
    hi,

    we can use materialized view to refrence another materialized view, As the_duck mentioned, check for refresh times.

    Check whether the tables are available in your schema or in different schema or in remote server.

    Thanks.

Posting Permissions

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