Unanswered: problem importing materialized view in a different schema
I have some problems while importing materialized view from one schema to another.I think it is because of difference in schema.
he schema in DB1 is user1 and the one in DB2 is user2
Following is the scenario:
I have a materialized view created with REFRESH FORCE ON COMMIT option. However when I create another schema importing all the objects from the current schema, I get an error. Following is the statement i'm using to create materialized view:
CREATE MATERIALIZED VIEW MV_TEST
REFRESH FORCE ON DEMAND
START WITH TO_DATE('05-06-2009 02:00:00', 'DD-MM-YYYY HH24:MIS') NEXT ROUND(SYSDATE + 1) + 2/24
SELECT DISTINCT(A.COL1), A.COL2, B.COL3, B.COL4
FROM TableA@dblink1 A, TableB@dblink1 B
WHERE A.COL1=B.COL3 AND A.COL2=B.COL4;
I get the following error in the log file for my import command:
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE SNAPSHOT "MV_TEST" USING ("MV_TEST", (8, 'DB1.REGR"
"ESS.RDBMS.DEV.US.ORACLE.COM', 1, 0, 0, "ARADMIN", "ORGANISATION", '2009-05-"
"11:02:00:04', 0, 0, '2009-05-11:02:00:04', '', 0, 9204404780155, 0, NULL), "
"1075839296, 5, ('2009-04-30:11:40:44', 0, 0, 0, 9206501216513, 0, 0, 2, NUL"
"L, NULL), '@dblink1.REGRESS.RDBMS.DEV.US.ORACLE.COM') REFRESH FORCE AS"
"SELECT DISTINCT(A.COL1), A.COL2, B.COL3, B.COL4"
"FROM TableA@dblink1 A, TableB@dblink1 B"
"WHERE A.COL1=B.COL3 AND A.COL2=B.COL4"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 12003:
"ALTER SNAPSHOT "MV_TEST" COMPILE"
IMP-00003: ORACLE error 12003 encountered
ORA-12003: materialized view "DB2"."MV_TEST" does not exist
If I give "Create any Materialized Views" and "Create any Tables" privilege to the user2, does it make sure that it will not take the schema name user1 while importing the data for the materialized views. Currently I have these 2 schema's and i do not face any problem in the import of the tables. But i do get the above errors in the materialized views. Why does it work with tables and not MVs?