Anyone have experience with the mystical Oracle ARCSDE_IDPIPE's?

[RobertAppleby]

ESRI 10.2.2
Oracle 11g
Non-Versioned DB, Default only, Direct Connect.

We migrate data routinely using Oracle expdp and impdp from Production to QA and DEV.
Before migrating the data all downstream constraints and triggers are disabled.
Data is imported using standard Oracle impdp.
Sequences reset including all R# using MAX(OBJECTID)
All existing ARCSDE_IDPIPE's are removed "dbms_pipe.remove_pipe".
All constraints and indexes are rebuilt including R#_SDE_ROWID and triggers re-enabled.

Randomly, in ArcMap users will get a unique constraint violation error. It appears placing a new feature using the GDB schema account repairs without having to re-register the tables. Other feature classes it's not an issue.

Suggestions please, don't make product or additional product recommendations?

[EmanuelMahler1]

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.