Friday, May 19, 2006

Fast Refreshing a Materialized View

Just a quick post about fast refreshing an MV.

Example:
schema1 in db1 has a database link to schema2 in db2

schema2 has select on table t1 in schema3 in db2

You want to create a fast refreshable MV in schema1 on table t1.

You must create a materialized view log on t1 and grant select on the log to schema2:

SQL> connect schema3@db2
Enter password: ********
Connected.
SQL> create materialized view log on t1;

Materialized view log created.

SQL> grant select on mlog$_t1 to schema2;

Grant succeeded.

If you neglect to do the grant, you will get the following error:

ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-02063: preceding line from LINK_TO_ESUBMIT
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2

Seems like there could be a better error message, but that's what you get.



3 comments:

Anonymous said...

Thanks a ton!! you save a lot of time!

rudibravo said...
This comment has been removed by the author.
rudibravo said...

Please, spread this to the world!! Spent hours trying to figure this out and all other threads and blogs never mentioned this!