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 ….
Orama.blog 