ORA-00955 when trying to create a Materialized view

We are trying to create a materialized view in APPS schema, but get the below error when running the create script
ORA-00955: name is already used by an existing object


It was verified that there is no object with the same name.
SQL> select name from v$database;

NAME
———
CDBTST2

SQL> show user
USER is “APPS”
SQL> select object_name, object_type
2 from all_objects
3 where object_name = ‘HFT_PURCHASE_ORDERS_MV’;

no rows selected

Based on some MOS notes and research, we have tried the following:

1. Purged recycle bin
2. Purged Shared Pool
3. Purged buffer cache
4. Bounced EBS and DB
5. No errors in alert.log

However, there seems to be some entries in the sys.obj$ table

SQL> select obj#, dataobj#, owner#, type#, name , ctime, mtime, stime
from sys.obj$
where name=’HFT_PURCHASE_ORDERS_MV’; 2 3

OBJ# DATAOBJ# OWNER# TYPE#
———- ———- ———- ———-
NAME
——————————————————————————–
CTIME MTIME STIME
—————— —————— ——————
2820785 685 5
HFT_PURCHASE_ORDERS_MV
16-JUL-22 16-JUL-22 16-JUL-22

3111742 1053 10
HFT_PURCHASE_ORDERS_MV
20-OCT-22 03-NOV-22 31-DEC-12

OBJ# DATAOBJ# OWNER# TYPE#
———- ———- ———- ———-
NAME
——————————————————————————–
CTIME MTIME STIME
—————— —————— ——————

2896568 2820785 871 88
HFT_PURCHASE_ORDERS_MV
05-OCT-22 05-OCT-22 00-DECEMBER-00

Fix

Check if there are any objects in ALL_OBJECTS_AE with the same name as the mview you are trying to create.

Then drop any views, synonyms etc

alter session set edition=ORA$BASE ;
SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_EDITION_NAME’) AS edition FROM dual ;

drop view …..

drop synonym ….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s