Skip to main content
Skip table of contents

(Oracle) Cannot create Constraint (Primary Key/Unique constraint) ORA-0095: name is already used by an existing object

Problem

In some occasions the bypass will fail to restore a primary key or unique constraints with the message "ORA-0095: name is already used by an existing object". When exploring the database, you notice that the relevant constraint is not present in the database. This occurs when, for some reason, the recreating of the constraints also contains the create index ddl statement. The index however is already created in previous step. 

For Example:

CODE
  ALTER TABLE "TDG_TEST"."SUPPLIER2" ADD PRIMARY KEY ("SUPPLIER_ID")
  USING INDEX (CREATE INDEX "TDG_TEST"."SUP_IDX2" ON "TDG_TEST"."SUPPLIER2" ("SUPPLIER_ID", "SUPPLIER_NAME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" )  ENABLE

The DATPROF bypass system for Oracle asks for each object to give their DDL statement and saves that in the DPF_BYPASS_CNTRL table. For both the index as the constraint the DDL statement is saved. When recreating the objects the indexes will be recreated first and than the constraints. However in this case the constraint would fail because it also wants to create the index as well, that is already being created.

The problem is that those indexes/constraints cannot be identified within the database to exclude them. 

Solution

You can easily remove those indexes from DPF_BYPASS_CNTRL table to prevent DATPROF to recreate them separately and leave it up to the constraint ddl statement. 

If your data model contains such constraints/indexes, create a script inside your project. Make sure that the script is executed "Before main process". 


Script

SQL
delete DPF_BYPASS_CTRL c where exists  
(select dbci.ownerschema, dbci.entityname
from DPF_BYPASS_CTRL dbcc
join all_constraints ac 
on  ac.owner  = dbcc.ownerschema
and ac.constraint_name  = entityname
join DPF_BYPASS_CTRL dbci
  on  nvl2(index_owner,index_owner,owner) = dbci.ownerschema
  and ac.index_name = dbci.entityname
where dbcc.bypass_type != 'index' and  DBMS_LOB.INSTR( dbcc.CREATE_DDL, 'USING INDEX (CREATE ' ) > 0
and c.ownerschema = dbci.ownerschema
  and c.entityname = dbci.entityname )


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.