problem importing materialized view

[needy]

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
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;

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

[PAVB]

I remember bumping into that same issue several Oracle versions ago -not sure if it is supposed to work now.

As far as I remember Oracle didn't allowed to import a Materialized View into a different schema -this was enforced by Oracle during export by fully qualifying the object creation which would conflict with to_schema option during import.

Being a materialized view I would manually create it.

[mahajanakhil198]

IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
IMP-00017: following statement failed with ORACLE error 12003:

I have not tried importing an MV into another schema but what I want to emphasize is that error list signifies the lack of privilege for creating materialized view. Did u give 'create materialized view' or 'create any materialized view' privilege to user2?

[needy]

what about the first error?

IMP-00017: following statement failed with ORACLE error 1031:
"CREATE SNAPSHOT "MV_TEST" USING ("MV_TEST", (8, 'DB1.REGR".....

[mahajanakhil198]

Have u given privilege 'create any materialized view' or 'create materialized view' to user2?

As far as I know, snapshot syntax is also like MV. So there may be any relation b/w them..But I dont know much abt snapshot

[needy]

Yes the user has the "Create Materialized View Privilege"

[needy]

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?

[PAVB]

Why does it work with tables and not MVs?

I told you why 14 days ago today; check Metalink Doc ID: 294625.1

[needy]

Yes I know you gave me the answer. But now I want to know if i grant the second user the privileges to "Create any Materialized Views" and "Create any Tables" and then create the materialized view manually and then i import the database from one schema to the other. Does it work? I have to explain this to the DBA before he can grant the desired permissions to the user. (you see he is a little more difficult :P)

[PAVB]

Tell your DBA to check Metalink Doc ID: 294625.1 then ask him to provide/suggest a suitable process to solve business requirements.