Compress encounters a DBMS unique constraint error

Error Message

When performing a compress operation, the following error may be encountered:

"Underlying DBMS error[ORA-00001: unique constraint (SDEADMIN.A789_PK) violated][SDE.DEFAULT]
Failed to execute (Compress)."

The error with additional information is also written to the sde error log:

"[Tue Apr 29 18:24:18 2008] [2156433] [H0454] SDE_Oracle Error: 1 ORA-00001: unique constraint (SDEADMIN.A789_PK) violated

[Tue Apr 29 18:24:18 2008] [2156433] [H0454] DB_registered_table_trim_state (SDEADMIN.CDTJOBDESIGNUNIT,8321579) Execute error (1).
Error when updating kept rows in adds table.
[Tue Apr 29 18:24:18 2008] [2156433] [H0454] Warning: Unable to trim state 8313585->8321579 for table SDEADMIN.CDTJOBDESIGNUNIT, error = -51
[Tue Apr 29 18:24:18 2008] [2156433] [H0454] DBMS error code: 1
ORA-00001: unique constraint (SDEADMIN.A789_PK) violated."

Cause

The unique constraint error indicates there are inconsistent entries present in the versioned table's delta tables, which are causing rows to become duplicates.

The compress operation encounters the error when compressing a lineage of states into one state. In the lineage being trimmed, there are multiple entries in the delta tables referencing a given ROWID value (ObjectID), which result in duplicate values.

Solution or Workaround

1. Identify the name of the versioned table as the input (-r) table_name argument for the sdegdbrepair command. Using the numeric identifier from the adds table primary key reported in the compress error, "ORA-00001: unique constraint (SDEADMIN.A789_PK) violated", query the SDE schema's table_registry to obtain the table name.
   
   In this example, the error message reported the constraint violation on the A789_PK index. The numeric value, 789, represents the table's registration_id value. As any user, execute the following SQL statement in SQL*Plus:
   
   

   Code:
   SQL> SELECT table_name FROM sde.table_registry WHERE registration_id = 789;
   
   TABLE_NAME
   ----------------
   CDTJOBDESIGNUNIT

   
   The output from the statement is the name of the versioned table that encountered the unique constraint violation. The table name can be obtained by inspecting the ArcSDE error log and locating the line containing the error with the warning statement:
   
   

   Code:
   [Tue Apr 29 18:24:18 2008] [2156433] [H0454] Warning: Unable to trim state 8313585->8321579 for table SDEADMIN.CDTJOBDESIGNUNIT, error = -51

   
   
2. Identify which version contains the inconsistency within its lineage based upon the high state_id being used to perform the trim during the compress operation.
   
   By inspecting the ArcSDE error log, identify the line in the file with the following error message:
   
   

   Code:
   [Tue Apr 29 18:24:18 2008] [2156433] [H0454] Warning: Unable to trim state 8313585->8321579 for table SDEADMIN.CDTJOBDESIGNUNIT, error = -51
   [Tue Apr 29 18:24:18 2008] [2156433] [H0454] DBMS error code: 1
   ORA-00001: unique constraint (SDEADMIN.A789_PK) violated

   
   
   The line with the warning 'Unable to trim state' contains the value of the high state, 8321579 in this example, which is used by the trim operation. Using the high state_id value can then be set in the following SQL statement to list all the versions that are dependent on this state. As any user, execute the following SQL statement in SQL*Plus:
   
   

   Code:
   SQL> SELECT owner, name FROM sde.versions
     2  WHERE state_id IN
     3    (SELECT DISTINCT lineage_id 
     4     FROM sde.state_lineages
     5     WHERE lineage_name IN 
     6       (SELECT DISTINCT lineage_name
     7        FROM sde.state_lineages
     8        WHERE lineage_id = 8321579))
     9  ORDER BY sde.state_id;
   
   OWNER      NAME
   ---------- ----------
   SDE        DEFAULT

   
   In the above example, only the DEFAULT version is returned. If multiple versions are returned, only one version is required as the input (-V) argument to the sdegdbrepair command. There is no need to execute the sdegdbrepair command for each version returned from the query because the inconsistent data will be repaired for all dependent versions.
3. Execute the sdegdbrepair command. Using the table name obtained from the first step and a version name from the second step, the command can be executed to only repair the specific table and version verses running against the entire versioned geodatabase.
   
   

   Code:
   C:\> sdegdbrepair -o repair_tables -d oracle10g -r SDEADMIN.CDTJOBDESIGNUNIT -V DEFAULT -u sde -p sde
   ESRI ArcSDE Server Repair Utility Thu Aug 14 16:30:38 2008
   ----------------------------------------------------------------
   Repair Instance Delta Tables, Are you sure? (Y/N): y 
   
   Repair operation completed without error.