Materialised View - can't create with ON COMMIT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Materialised View - can't create with ON COMMIT

  1. #1
    Join Date
    Feb 2001
    Posts
    28

    Angry

    Hi ,

    I am trying to create MV:

    CREATE SNAPSHOT "OUTCOME"
    TABLESPACE "DEF01"
    BUILD IMMEDIATE
    REFRESH FORCE
    ON COMMIT
    ENABLE QUERY REWRITE
    AS
    SELECT
    ProjectID,
    Count(*) CountAll,
    Sum(prior_Other) priorActivityNS
    FROM Participant
    GROUP BY ProjectID;

    but I have this error msg:

    FROM Participant
    *
    ERROR at line 13:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    anyone has idea please ?

    Thanks, Jan







  2. #2
    Join Date
    Feb 2001
    Posts
    28
    If I've taken out Sum() it is Ok. But can't create with Sum() - don't know why. Other views with Sum() works.

  3. #3
    Join Date
    Feb 2001
    Posts
    28

    Thumbs up

    Hi,

    just found the problem in DataWarehouse Doc:

    Restrictions on Fast Refresh on Materialized Views with Single-Table Aggregates:

    If VARIANCE(expr) or STDDEV(expr) is specified, you must have COUNT(expr) and SUM(expr).

    it works after I've added count() there.

Posting Permissions

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


Click Here to Expand Forum to Full Width