Materilized Views with Auto Refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Materilized Views with Auto Refresh

  1. #1
    Join Date
    Jul 2000
    Location
    Mumbai
    Posts
    4

    Cool

    Hi Folks,
    What is the syntax for creating a materilized view which refreshes automatically when there is a change in the base tables. What are all the priviledges which are to be grant to user(say Scott) to execute Materilized view and achieve automatic refresh.

    Please provide detailed reply with all the features of Materilized Views.

    Thanks in advance




  2. #2
    Join Date
    Jun 2001
    Posts
    76
    Hi,

    http://lbdwww.epfl.ch/f/teaching/cou...ch4f.htm#24503

    That will show you the exact syntax.

    To automatically refresh when changes are commited on the master table, use the

    REFRESH ON COMMIT

    option.

    To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view's schema, and you must have the QUERY REWRITE system privelage. If the view and tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privelage.

    R
    On the other hand, you have different fingers.

  3. #3
    Join Date
    Jul 2000
    Location
    Mumbai
    Posts
    4

    Materilized Views

    I had granted Query Rewrite priviledge to Scott and tried to create the materilized view. Some how it was not creating, I created logging from System user in the Scott Schema by specifying create materilized view scott.empmet ----etc, I had created with build immediate option however when tried to give the option of 'Fast On Commit', it was not getting executed.

    Therefore I skipped 'Fast on Commit' and created Mat View and then executed 'Fast on Commit' through Alter Mat View command. It worked.

    However when I tried to insert the rows in the base table(which is in Scott schema and also Mat View is in Scott Schema) and tried comitting , it gave following error:

    COMMIT
    *
    ERROR at line 1:
    ORA-12032: cannot use rowid column from snapshot log on "SCOTT"."DEPT" Please let me know where I am going wrong.

    Thanks

  4. #4
    Join Date
    Mar 2001
    Posts
    188
    hi,
    you must grant the following option to scott.
    GRANT CREATE ANY SNAPSHOT TO "SCOTT"
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

  5. #5
    Join Date
    Jul 2000
    Location
    Mumbai
    Posts
    4

    Materilized View

    Hi Thomas,Rlowe,

    I had granted create any snapshot to scott, however when I tried to insert the records in emp table, it is still giving following error.


    SQL> insert into emp values(1111,'rao','dba',7839,'28-jan-01',2000,10,10);

    1 row created.

    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12032: cannot use rowid column from snapshot log on "SCOTT"."DEPT"

    Please advice where I am going wrong.

    Thanks

    Mettu

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