(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 requires each object to provide its DDL statement, which is then stored in the DPF_BYPASS_CNTRL table. The DDL statement is saved for both indexes and constraints. When the objects are recreated, the indexes are created first, followed by the constraints. However, in this scenario, the constraint creation fails because it attempts to create the index again, which 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 )