English

Error: Underlying DBMS error (ORA-00001: Unique constraint (SDE.GDB_OC_PKC) violated)

Error Message

The following actions may result in the error message:

"Underlying DBMS error (ORA-00001: Unique constraint (SDE.GDB_OC_PKC) violated)".

-Registering an SDE layer as versioned.
-Registering an SDE layer with the geodatabase.
-Registering an Oracle Spatial table with the geodatabase.
-Building a geometric network.
-Importing a shapefile, coverage, or a feature class from another geodatabase into ArcSDE by way of ArcCatalog.

Other operations may also cause this error.

Cause

This error occurs when the sequence that generates new values for the ID column of SDE.GDB_OBJECTCLASSES becomes out of sync with the values already in that column. This situation does not occur under normal circumstances, but possible causes include improper use of the Oracle import function, an Oracle shutdown during an ArcGIS transaction, or manual manipulation of the ArcSDE metadata tables.

Solution or Workaround

Advance the sequence to a higher value than the maximum value in the ID column of SDE.GDB_OBJECTCLASSES. Log in to SQLPlus as the SDE user to run the following SQL commands.

  1. Determine the maximum value in the ID column of SDE.GDB_OBJECTCLASSES.

    Code:
    SQL>SELECT MAX(id) FROM gdb_objectclasses;

    Result for this example:

    MAX(ID)
    ---------------
    55
  2. Determine the name of the sequence for the ID column. The naming convention is R<registration id> where the registration ID is the value in the registration_id column of SDE.TABLE_REGISTRY for the entry for the GDB_OBJECTCLASSES TABLE.

    Code:
    SQL>SELECT registration_id FROM table_registry WHERE table_name = 'GDB_OBJECTCLASSES';

    Result for this example:

    REGISTRATION_ID
    ---------------
    3
  3. Determine the next value to be supplied by the sequence:

    Code:
    SQL>SELECT last_number FROM user_sequences WHERE sequence_name = 'R3';

    Result for this example:

    LAST_NUMBER
    ---------------
    50

    In this example, the next ID that is provided by the sequence is 50. Since the highest ID in GDB_OBJECTCLASSES is currently 55, any values lower than 55 are already taken. Therefore ArcSDE attempts to insert a non-unique value into the column, violating the primary key constraint.
  4. In this example, the sequence needs to be advanced by six. Therefore run the following SQL statement six times:

    Code:
    SQL>SELECT R3.NEXTVAL from dual;

    Result for this example:

    NEXTVAL
    ----------
    56
  5. Verify that the sequence has advanced by running the SQL statement from step 1 again:

    Code:
    SQL>SELECT last_number FROM user_sequences WHERE sequence_name = 'R3';

    LAST_NUMBER
    ---------------
    56

    The ID in this example to be assigned, to the next row to be inserted into GDB_OBJECTCLASSES, is now 56. Since this is higher than the current maximum ID value in GDB_OBJECTCLASSES, there is no longer any possibility for the primary key constraint to be violated.

Related Information