Materialized View Not Refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Materialized View Not Refresh

  1. #1
    Join Date
    Nov 2007
    Posts
    5

    Materialized View Not Refresh

    MY TEST TABLE LIKE THAT
    SQL> SELECT * FROM TEST;

    ID NAME
    ---------- ----------
    1 A
    2 B

    SQL> DESC TEST
    Name Null? Type
    ------------------------------- -------- ----------
    ID NOT NULL NUMBER
    NAME VARCHAR2(10)


    I CREATE THE MATERIALIZED VIEW LOG LIKE...

    SQL> CREATE MATERIALIZED VIEW LOG ON TEST
    2 WITH PRIMARY KEY,ROWID;

    Materialized view log created.

    THAN I CREATE MATERIALIZED VIEW LIKE ....

    SQL> CREATE MATERIALIZED VIEW TEST_VIEW
    2 REFRESH FAST ON COMMIT AS SELECT ID,NAME
    3 FROM TEST;

    Materialized view created.

    SQL> SELECT * FROM TEST_VIEW;

    ID NAME
    ---------- ----------
    1 A
    2 B

    --INSERT A NEW ROW..

    SQL> INSERT INTO TEST1 VALUES(3,C);

    1 row created.

    SQL> COMMIT;

    Commit complete.

    THAN I REFERSH THE MATERIALIZED VIEW....

    SQL> EXECUTE DBMS_MVIEW.REFRESH('TEST_VIEW');

    PL/SQL procedure successfully completed.

    BUT WHAN I QUARY THE MATERIALIZE VIEW I DIDNT SEE THE NEW RECORD.

    SQL> SELECT * FROM TEST_VIEW;

    ID NAME
    ---------- ----------
    1 A
    2 B

    PLEASE TELL ME WHERE I MISTAKE...
    THANKS

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That's correct, your MV is showing what it has to show.

    Here is why...
    1- Your MV points to TEST table.
    2- You are inserting into TEST1 table.
    3-
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2007
    Posts
    5
    sorry sir, yes i was inserted test1 table.

    but now i insert data test table, but i didnt see new record in materialize view.

    i dont know where my mistake, or it is the right procedure refrash materialize view.

    thanks.

  4. #4
    Join Date
    Nov 2007
    Posts
    5
    im new in oracle,

    so anyone know any other option how materialize view are refresh

    please tell me .

    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
  •  



Click Here to Expand Forum to Full Width