I'm not sure this applies to you but we just had a case of duplicate OBJECTIDs because we have changed the increment of a sequence used for OBJECTIDs. The gdb_util.next_row_id function which is presumably also used by ArcMap "caches" OBJECTID values in these pipes called ArcSDE_IdPipe<registration_id>. As far as I understand, the function uses the current increment to determine the amount of OBJECTID values to hand out before calling the underlying oracle sequence again. However, this can result in duplicate OBJECTID values if the increment of the sequence is changed. Consider the following example: SQL> select registration_id from sde.table_registry where table_name = 'GDB_TABLE';
REGISTRATION_ID
---------------
290
SQL> select SDE.gdb_util.next_rowid('GISDATA', 'GDB_TABLE') NEXT_ID from dual;
NEXT_ID
-------
1
SQL> select SDE.gdb_util.next_rowid('GISDATA', 'GDB_TABLE') NEXT_ID from dual;
NEXT_ID
-------
2
SQL> alter sequence GISDATA.R290 increment by 1;
Sequence altered.
SQL> select GISDATA.R290.nextval from dual;
NEXTVAL
----------
2 So the OBJECTID 2 was provided twice. This also happens if the next_rowid function is called 15 times (that's the original increment value). It will then go back to the sequence and continue with the number 2. In case of a versioned dataset the insert would not fail but the compress would result in a ORA-00001: unique constraint violation. When changing the sequence it is not enough to purge the ArcSDE_IdPipe. You have to call <sequence_name>.nextval *before* reducing the sequence's increment to step over these "reserved" values that will be used or have already been used by the gdb_util.next_rowid function. I hope this could be useful for someone experiencing unique constraint errors.
... View more