ORA-12006: a materialized view with the same user.name already exists

I have a new empty schema and I'm trying to create a new materialized view the problem is that I'm having an ORA - 12006 error and I don't know wy because like I've said, the shema is empty and there's anything data in it yet...

I have a new empty schema and I'm trying to create a new materialized view the problem is that I'm having an ORA - 12006 error and I don't know wy because like I've said, the shema is empty and there's anything data in it yet...

My query seems like:

CREATE MATERIALIZED VIEW mySchema.mvName (column1,column2) TABLESPACE myTablespace REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY AS SELECT DISTINCT column1,column2 AS alias FROM anotherSchema.table@dblink WHERE condition1; /

I have alredy create the grants to the dblink.

Could anybody hep me? Thanks!!

1

2 Answers

If the code in the question is exactly what you run in SQL*Plus then this is expected.

You don't need a ; and a / for a SQL statement in SQL*Plus. The ; is enough. You only need the / for PL/SQL.

In fact in your case the / is the reason why you get the error.

In a nutshell: when SQL*Plus reaches the ; it creates the materialized view. It then reaches the / which means "run the statement in the buffer". And therefor the create mview is executed again - obviously generating an error.

See my related answer regarding this topic for more details:
https://stackoverflow.com/a/10207695/330315

2

Simply you can check if you have access to the dictionaries

select object_name,object_type,owner from dba_objects where object_name='yourobject';

Or a very basic check with select

SELECT * FROM <source user>.<source table>@<database link>

Even Sylvain's question is very much applicable

DROP MATERIALIZED VIEW myschema.mvname;

and then try to create view.

If the error is for snapshot, then check for duplicates in it

SELECT * FROM all_snapshots ;

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaW9uZ2h%2BcoKOqKmaZWFnfXGCjJpkppmkmr%2BqrcuisZ6cXau2psOMsKCtoF2ptaZ50pqknmWlqLKzec2apJ5lkaG%2Fpq3DsmSesJmowbQ%3D

 Share!