Oracle 22.214.171.124 Windows (yuk) 64 bit.
Toad 126.96.36.199 with DBA module.
I have a script generated from the schema browser -> Materialised Views -> Script tab on right hand side.
When I execute that script in QSR, it barfs at the creation of a unique index corresponding to the primary key. The error is ORA-00955: name is already used by an existing object.
The MV is based on a single table, and the relevant part of the script is:
DROP MATERIALIZED VIEW USER.TROUBLED_MV;
CREATE MATERIALIZED VIEW USER.TROUBLED_MV (AAA, BBB, CCC)
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
– This fails, “ORA-00955: name is already used by an existing object”
CREATE UNIQUE INDEX USER.TROUBLED_PK1 ON USER.TROUBLED_MV(AAA)
It seems that because the WITH PRIMARY KEY clause is specified, then Oracle creates the PK at the time it creates the MV.
The PK on the base table is called TROUBLED_PK.
The PK on the MV is called TROUBLED_PK1, which is the same name as the unique index that the script is trying to create.
I suspect that Toad is most likely getting the script using DBMS_METADATA or similar? In which case there’s probably not much we can do, but I suspect that if the WITH PRIMARY KEY is specified, there’s no need to execute the code to create the unique index. Perhaps!
Not urgent, I know the problem and can work around it.
Disclaimer: It’s not my fault that Oracle cant spell “materialised” properly!