Materilized Views with Auto Refresh

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

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

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

hi,
you must grant the following option to scott.
GRANT CREATE ANY SNAPSHOT TO "SCOTT"

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