DBMS_Snapshot.refresh not working on 11g , materialized view error

Question

I executed DBMS_Snapshot.refresh on Oracle 10g it worked fine, but when i execute the same on Oracle 11g it gives the following error

DBMS_SNAPSHOT.refresh('Table1','F'); 

 BEGIN DBMS_SNAPSHOT.refresh('Table1','F'); END; 
 . 
 *

 ERROR at line 1: 
 ORA-23401: materialized view "localuser"."Table1" does not 
 exist 
 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558 
 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771 
 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740 
 ORA-06512: at line 1

Any help much appreciated

Regards.

Nandish

Answer 1

OK, there may be many reasons for this.

1. You didn't recreate the MV yet in 11g. You can't refresh an MV to create it.

2. You didn't recreate a synonym (public or private) to the schema where the MV is located.

3. You didn't recreate grants to the MV in another schema, so create them.

You might try DBMS_MVIEW instead of DBMS_SNAPSHOT.

exec dbms_mview.refresh('Table1');