Failed to compress the database. Underlying DBMS error[ORA-00001: unique constraint

[EmadAl-Mousa]

The following is the error faced while trying to compress the Geodatabase:

Failed to compress the database.
Underlying DBMS error[ORA-00001: unique constraint(constraint name) violated]
[SDE.DEFAULT]

Geodatabase Environment:
ArcSDE Release: ArcSDE Oracle_11g (64-bit) 9.3.1 Service Pack 2
DBMS Release: Oracle 11gR2 (PSU 11.2.0.1.4)
Server: IBM AIX 6.1

Troubleshooting and Resolution:

ArcSDE Logs:
check the arcsde log file named : sde_esri_sde.log

The log file location is under $SDEHOME/etc directory

In UNIX you can use the command:
tail -f sde_esri_sde.log

log file output would be similar to this:

[Sat Feb 2 07:10:02 2013] [550533] Warning: Unable to trim state 850980->851105 for table XXX, error = -51
[Sat Feb 2 07:10:02 2013] [550533] DBMS error code: 1
ORA-00001: unique constraint (Schema.A4327_PK) violated

Solution:
Reference document: http://support.esri.com/en/knowledgebase/techarticles/detail/35298

Execute the following queries:

SELECT table_name FROM sde.table_registry WHERE registration_id =XX;
XX: is the index name found in the error message

another query:
SELECT owner, name FROM sde.versions
WHERE STATE_ID IN
(SELECT DISTINCT lineage_id
FROM SDE.STATE_LINEAGES
WHERE lineage_name IN
(SELECT DISTINCT lineage_name
FROM SDE.STATE_LINEAGES
WHERE lineage_id = XXX))
ORDER BY STATE_ID;

Remark: this query has typos in the ESRI reference document, instead of (ORDER BY sde.state_id) replace it with (ORDER BY STATE_ID) because itâ??s a column under sde.versions table.
XXX: is the number found in the error log message â??Warning: Unable to trim state 850980->851105â?? which is 850980 in our case.

To Fix the problem you need to execute the following command:

sdegdbrepair -o repair_tables -d oracle11g -r schema.feature class name -V DEFAULT -u sde -p password

Remark:[/B] No editors should be connecting while executing this command.

Regards,
Emad Al-Mousa

[EmadAl-Mousa]

correction:
XXX: is the number found in the error log message â??Warning: Unable to trim state 850980->851105â?� which is 851105 in our case.

upper boundry

[EmadAl-Mousa]

this command is better:
sdegdbrepair -o repair_tables -d ORACLE11G -V state:XX -u XXX -p YYY

where:

XX: is the state upper bound number
XXX: sde user
YYY: sde password