If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

problem importing materialized view

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:

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.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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?

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?

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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)

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.