MVIEW on commit refresh condition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: MVIEW on commit refresh condition

  1. #1
    Join Date
    Mar 2002
    Posts
    46

    Exclamation MVIEW on commit refresh condition

    Hi,


    I need to have an up to date copy of a table in another environment. In the master database I created a materialized view log on the master table:

    CREATE MATERIALIZED VIEW LOG ON TIPS.POSITIONS WITH ROWID INCLUDING NEW VALUES;

    Actually all applications use a DBLINK to access the positions table in the remote database. I tried to create a snapshot to have better performance:

    CREATE MATERIALIZED VIEW MV_POSITIONS
    REFRESH FAST ON COMMIT
    AS
    SELECT *
    FROM POSITIONS

    An I got the error:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    What are those conditions?

    I read in the oracle documentation about on commit refresh:
    Restriction: This clause is supported only for materialized join views and single-table materialized aggregate views

    Does this mean I can't do a on commit fast refresh on a single table??

    Thanks
    DONTBSHY

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Did you create a materialized view log for the underlying table?
    Did you try REFRESH FORCE ?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Mar 2002
    Posts
    46
    Hi,
    Yes I tried REFRESH FORCE and I got the same error. By :


    Originally posted by Mr.Hanky
    Did you create a materialized view log for the underlying table?
    Did you try REFRESH FORCE ?

    MH
    You're talking about which table? I created a log on the master table and I'm trying to create an on commit refresh snapshot for this table.

    Thanks
    DONTBSHY

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    I'm not sure but do you need to specify...

    CREATE MATERIALIZED VIEW MV_POSITIONS
    REFRESH FAST ON COMMIT
    AS
    SELECT *
    FROM POSITIONS

    BUILD IMMEDIATE;


    after the from positions?

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Mar 2002
    Posts
    46
    This doesn't work.. The documentaion doesn't mention it...

    I'm still looking for those condition!
    DONTBSHY

  6. #6
    Join Date
    Mar 2002
    Posts
    46
    Hi,

    I got the answer for my problem:

    8.1.6 Data Warehousing Guide (8-20) : ON COMMIT : Can be used with
    materialized views on single table aggregates and materialized views
    containing joins only.

    9.0.1 Data Warehousing Guide (8-26) : ON COMMIT : Can be specified as long as the materialized view is fast refreshable (in other words not complex).

    The query you listed is a single table non-aggregate and should thus
    be able to be ON COMMIT refreshed in 9.x but not in 8.1.x.

    I'm using 8.1.7.4... Dammit !
    DONTBSHY

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Oh well, glad you found it.

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    if uses view materialized is necessary to set something in the configuration archive it Oracle?
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  9. #9
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97

    Unhappy

    SQL> CREATE MATERIALIZED VIEW vw_teste
    2 BUILD IMMEDIATE
    3 REFRESH FAST ON COMMIT
    4 as
    5 SELECT codigo, nome
    6 FROM marcio.teste@pd00
    7 ;
    SELECT codigo, nome
    *
    ERROR at line 5:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    SQL> CREATE MATERIALIZED VIEW vw_teste
    2 PCTFREE 0 TABLESPACE usr
    3 BUILD IMMEDIATE
    4 REFRESH COMPLETE ON DEMAND
    5 AS
    6 SELECT codigo, nome
    7 FROM marcio.teste@pd00
    8 ;

    Materialized view created.

    BUT do not refresh

    Last edited by marcio.68; 05-12-2003 at 12:53 PM.
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

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