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 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!!
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:
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 ;
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
