Introduction
While working with Oracle databases, you may encounter the error ORA-00955: name is already used by an existing object when trying to create objects like tables, indexes, or materialized views. This error indicates that an object with the same name already exists in the schema, causing a naming conflict.
In this blog, we will walk through a real-world example of resolving the ORA-00955 error when creating a materialized view named EMP_LIST_VERSION1 in the Stage database. We’ll explain why the error occurred and the step-by-step solution to fix it using Oracle Data Pump export/import utilities.
Issue Description
While executing the following command to create a materialized view:
Oracle returned the error:
ORA-00955: name is already used by an existing object
This happened because there was already an object named EMP_LIST_VERSION1 in the same schema. In this case, the existing object was a table, which caused the conflict since Oracle does not allow two objects with the same name in one schema.
Steps to Resolve the Issue
Step 1:
Export the Materialized View from Production
Since the materialized view was successfully created in the Production environment, we used Oracle Data Pump Export to extract only the materialized view object:
[oracle@ ~]$ export ORACLE_SID=prod_db_name
[oracle@ ~]$ expdp \”/ as sysdba\” directory=SCHDUMP dumpfile=emp_list_version1_mv.dmp logfile=emp_list_version1_mv_exp.log schemas=your_schema_name include=”MATERIALIZED_VIEW:\”=’EMP_LIST_VERSION1’\””
Export: Release 19.0.0.0.0 – Production on Mon Aug 11 17:05:29 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″: “/******** AS SYSDBA” directory=SCHDUMP dumpfile=emp_list_version1_mv.dmp logfile=emp_list_version1_mv_exp.log schemas=your_schema_name include=MATERIALIZED_VIEW:”=’EMP_LIST_VERSION1′”
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/emp_list_version1_mv.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Aug 11 17:05:59 2025 elapsed 0 00:00:29
This command exports the materialized view EMP_LIST_VERSION1 from the your_schema_name schema into a dump file.
Step 2:
Import the Materialized View into the Stage Database(Target Database)
Next, we imported the materialized view dump file into the Stage database:
This restored the materialized view in the target schema without conflicting with the existing table.
[oracle@ ~]$ impdp \”/ as sysdba\” directory=SCHDUMP dumpfile=emp_list_version1_mv.dmp logfile=emp_list_version1_mv_imp.log schemas=your_schema_name include=”MATERIALIZED_VIEW:\”=’EMP_LIST_VERSION1’\””
Import: Release 19.0.0.0.0 – Production on Mon Aug 11 20:23:24 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Master table “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_SCHEMA_01″: “/******** AS SYSDBA” directory=SCHDUMP dumpfile=emp_list_version1_mv.dmp logfile=emp_list_version1_mv_imp.log schemas=your_schema_name include=MATERIALIZED_VIEW:”=’EMP_LIST_VERSION1′”
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job “SYS”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Mon Aug 11 20:23:28 2025 elapsed 0 00:00:03
Step 3:
Verify the Objects
After import, verify that both the table and materialized view named EMP_LIST_VERSION1 exist:
Here we could see entries for both the TABLE and MATERIALIZED VIEW, which confirms that the materialized view was imported successfully.
Conclusion
The ORA-00955 error is a common issue caused by naming conflicts in Oracle schemas. When we encounter this while creating a materialized view, it is crucial to check for existing objects with the same name.
In our scenario, the conflict was due to a table already named EMP_LIST_VERSION1. The best way to resolve this without renaming or dropping existing objects is to export the materialized view from the source environment and import it into the target environment using Data Pump utilities.
By following these steps, we can safely migrate materialized views and avoid naming conflicts, ensuring smooth database object management.
