Bug in script generation for Materialised view, perhaps

Toad for Oracle
1

Morning All,

Oracle 11.2.0.4 Windows (yuk) 64 bit.

Toad 12.1.0.22 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)
…
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT /+APPEND/
…
FROM A_SINGLE_TABLE;

– 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! :wink:

Cheers,

Norm. [TeamT]

2

Norm!

It’s a known issue. I don’t have a way of distinguishing these auto-generated indexes from one that someone might have created manually, so I figure it’s better to add the statement that might fail with an ‘already exists’ error rather than leave it off and possibly leave off a user-generated index.

Sometimes these things change as Oracle versions evolve, so I just expanded your example a little bit and retested…same result. No way that I can see to tell a user-generated index from a system generated index (in this case anyway - there is a GENERATED column but it says N in both user_obejcts and user_indexes)

drop table a_single_table

create table a_single_table
(aaa number,
bbb number,
ccc number);

ALTER TABLE A_SINGLE_TABLE ADD
CONSTRAINT A_SINGLE_TABLE_PK
PRIMARY KEY (AAA);

create materialized view log on a_single_table;

CREATE MATERIALIZED VIEW TROUBLED_MV (AAA, BBB, CCC)
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT /+APPEND/ *
FROM A_SINGLE_TABLE;

CREATE INDEX ind_user_generated ON JDORLON.TROUBLED_MV
(BBB);

select index_name, generated
from user_indexes
where table_name = ‘TROUBLED_MV’;

select object_name, generated
from user_objects
where object_name in (select index_name from user_indexes where table_name = ‘TROUBLED_MV’);

-John

3

Morning John,

I thought it would be something like that. No worries, I’ll cope!

Have a good weekend.

Cheers,

Norm.