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

Question

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

What's the output of `DROP MATERIALIZED VIEW mySchema.mvName` ? — Sylvain Leroux, Oct 31 '14 at 11:09

score 3 · Accepted Answer · edited May 23 '17 at 12:14

3

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

edited May 23 '17 at 12:14

Community

1
1

answered Oct 31 '14 at 11:31

Yep!!! You're right!! I'm using sqlPlus but the script that I'm executing has been given by Toad, so the / character is given automactly. Removing it, it works fine!! Thanks and thank you all for your time! – Oct 31 '14 at 11:39
You don't need a parachute to skydive. You need a parachute to skydive twice ;-) – Lalit Kumar B Oct 31 '14 at 11:42

score 1 · Answer 2 · answered Oct 31 '14 at 11:21

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 ;

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

2 Answers2