Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Oracle 8i ORA-12054: cannot set the ON COMMIT refresh attribute for materialized view

    Hi all,

    I am trying to create a simple materialized view on a table with REFRESH FAST ON COMMIT attribute. But I am getting this error. Please tell me where I am wrong? I have also created materialized view log on the table.

    Syntax:

    create materialized view log on comh_client;

    create materialized view matv1
    PARALLEL
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS SELECT client_cd, brch_cd from comh_client;

    thanks,
    Abhijit

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What errors are you getting? Please post the entire interaction from when you tried to issue the create in sql*plus.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2004
    Posts
    4

    Oracle 8i - Materialized view ON COMMIT error

    hi beilstwh,

    thanks for ur attention. here are the steps.

    1) I have a table comh_client. in which there are 2 columns client_cd and brch_cd.
    2) I want to create a ON COMMIT materialized view which will automatically get refreshed as and when I do update/insert/delete on comh_client
    3) so I logged on to SQL Plus.
    4) create materialized view log on comh_client; (this works fine)
    5) then I tried to create mat view as below

    create materialized view matv1
    PARALLEL
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS SELECT client_cd, brch_cd from comh_client;

    6) where Oracle is giving me error : ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    can you please help me out why is this error?

    regards,
    Abhijit

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    is the comh_client table an IOT or a view? They can't be used with the on-commit.
    Last edited by beilstwh; 10-25-04 at 09:59.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Oct 2004
    Posts
    4

    Angry

    it's a simple, user created table owned by the same user. I am doing all this stuff in one user so there shouldn't be any Grant related problems.

    I am hearing from some of my friends that there is a bug in Oracle 8.1.7 which prevents user to create a materialized view with REFRESH FAST ON COMMIT attribute. is this true?

    regards,
    Abhijit

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Have you created a materialized view log on comh_client table? A materialized view log must exist on the table and must contain all columns referenced in the materialized view. The log must have been created with the INCLUDING NEW VALUES clause.

    See the following link on MVL's

    http://www.oracle.com/pls/tahiti/tah...?section=74263
    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
  •