(Oracle) Cannot create Constraint (Primary Key/Unique constraint) ORA-0095: name is already used by an existing object
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.
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.
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".
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 )