ArcGIS版本压缩（Compress）报ORA-00001: unique constraint 的解决方法

在使用ArcGIS版本压缩，经常会出现这样那样的问题，这个版本压缩就是一个等待的过程，如果出现问题，我们很难来确定问题的根源在哪里，下面我们就针对几个比较典型的压缩错误来看看怎么解决？

sdeversion -o compress -u sde -p sde

ESRI ArcSDE Server Repair Utility
------------------------------------------------------------
Compress state tree: Are you sure? (Y/N): y
Error: Underlying DBMS error (-51).
Error: Unable to compress state tree.
ORA-00001: unique constraint (GDB.A40_PK) violated

从上面的错误信息我们也很容易的知道是A40这个表的主键重复造成的，那么这种问题我们该怎么修改呢，如果对ArcSDE的表结构或者数据库知识必须熟悉的人可能直接从数据库的层面上来解决这个问题了，那么如果对这些不熟悉，我们一样可以解决，因为ArcSDE提供了一个命令来检查和修复这些错误。

C:\Users\Administrator>sdegdbrepair

ArcSDE 10.0 Repair
Repair   Administration Utility
-----------------------------------------------------


Usage for sdegdbrepair:

-?
-h

-o diagnose_tables   -d {ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}
                    [-r {<versioned_table_name> | file=<table_list>}]
                    [-V {ALL | NONE | <version_name> | STATE:<state_id>}]
                    [-H <sde_directory>] [-u <DB_Admin_user>] [-p <DB_Admin_password>]
                    [-D <database>] [-s datasource] [-q]
                    [-i <master_geodatabase_service>:<user_schema>]
-o repair_tables     -d {ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}
                    [-r {<versioned_table_name> | file=<table_list>}]
                    [-V {ALL | NONE | <version_name> | STATE:<state_id>}]
                    [-H <sde_directory>] [-u <DB_Admin_user>] [-p <DB_Admin_password>]
                    [-D <database>] [-s datasource] [-N] [-q]
                    [-i <master_geodatabase_service>:<user_schema>]
-o diagnose_metadata -d {ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}
                    [-H <sde_directory>] [-u <DB_Admin_user>]
                    [-p <DB_Admin_password>] [-D <database>] [-s datasource]
                    [-i <master_geodatabase_service>:<user_schema>] [-q]
-o repair_metadata   -d {ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}
                    [-H <sde_directory>] [-u <DB_Admin_user>] [-N]
                    [-p <DB_Admin_password>] [-D <database>] [-s datasource]
                    [-i <master_geodatabase_service>:<user_schema>] [-q]

sdegdbrepair这个命令可以对所有注册版本的表出现增加表（A表）和删除表（D表）出现不一致的情况进行修复。这种不一致的情况有很多：
1：Orphaned rows（孤立的行）
2：Duplicate entries in deletes tables（D表有重复记录）
3：Entries missing from the deletes tables（D表有丢失记录）
4：No inconsistencies（状态值的上下节点可能无效，可能找不到等）

而且sdegdbrepair命令可以不仅针对所有的表和版本进行检查和修复，也可以对某个版本、某个表、某个状态进行检查和修复。
我们来看一下针对metadata的修复规则
1：如果版本指向了一个不存在的状态，默认指向其父版本
2：如果DEFAULT版本指向不存在的状态，默认更新其执行状态值=0
3：某个状态有一个无效的父状态不被修复，但是会报警告
4：STATE_LINEAGES表中记录包含额外的lineage_id将被移除
5：MVTABLES_MODIFIED 表中记录包含的State ID指向不存在的状态将被移除

针对Delta表的修复规则
1：A表中涉及不存在的状态的记录将被删除
2：D表中涉及不存在的状态的记录将被删除
3：当多版本的记录包含同一个状态，最近的记录被删除
4：同一个要素在A表和Base表中同一个状态被多次删除，最近删除的记录被移除

以下为英文描述，请参考：

The repair rules for metadata are as follows:

Versions pointing at a nonexistent state will be made to point to the state of its parent version if possible. If the DEFAULT version is pointing at a nonexistent state, it will be updated to point at the base state (0).
States that have an invalid parent state are not repaired, but a warning is issued.
Records containing an extra lineage_id in the STATE_LINEAGES table will be removed.
Records containing state IDs in the MVTABLES_MODIFIED table that are pointing to nonexistent states will be removed. 


The repair rules for the delta tables are as follows:

Records in the Adds table that reference nonexistent states are deleted. 
Records in the Deletes table that reference nonexistent states are deleted. 
When multiple versions of a record are present in the same state, all but the latest record is deleted. 
When the same feature in the Adds or Base table is deleted multiple times in the same state, all but the latest delete entry is removed. 

我们来看一下执行该命令的步骤

1：对数据库做好备份
2：针对Oracle数据库来说对SDE用户只需如下权限

ALTER ANY INDEX
ALTER ANY TABLE
ANALYZE ANY
CREATE ANY INDEX
CREATE ANY PROCEDURE
CREATE ANY SEQUENCE
CREATE ANY TRIGGER
CREATE ANY VIEW
CREATE SESSION
DROP ANY INDEX
DROP ANY TABLE
DROP ANY VIEW
DROP ANY PROCEDURE
DROP ANY SEQUENCE
EXECUTE ANY PROCEDURE
SELECT ANY SEQUENCE
SELECT ANY TABLE
UNLIMITED TABLESPACE

3：确保在修复过程中没人对geodatabase进行编辑

针对上面抛出来的问题A40表出现问题，我们可以根据A40表，将Base表名找到，然后执行以上命令进行检查和修复。

sdegdbrepair -o repair_tables -d ORACLE10G -r GDB.TOPO_AREA -V GDB.V1 -p sde

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.

我们可以查看SDEHOEM/etc/sde_repair.log查看

[Wed Sep 06 15:16:29 2006] Mvdata Clean: 1 duplicates found in GDB.TOPO_AREA at state 6315, lineage name 6162.
[Wed Sep 06 15:16:29 2006] Mvdata Clean: Successfully cleaned GDB.TOPO_AREA
[Wed Sep 06 15:16:29 2006] Clean: 0 delete rows removed, 1 delete rows recreated.

例如在实际过程中也可能出现

sdeversion -o compress -u sde -i 3666 -s bigserver

Version                  Administration Utility
-----------------------------------------------
Enter DBMS User Password:
Compress state tree: Are you sure? (Y/N): y

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

我们查看SDEHOME日志看到

[Tue Oct 7 12:23:18 2008] [16301] SDE_Oracle Error: 1 ORA-00001: unique constraint (SHALA.A600_PK) violated

[Tue Oct 7 12:23:18 2008] [16301] DB_registered_table_trim_state (SHALA.VACANTLOTS,91370) Execute error (1).
Error when updating kept rows in adds table.
[Tue Oct 7 12:23:18 2008] [16301] Warning: Unable to trim state 91349->91370 for table SHALA.VACANTLOTS, error = -51
[Tue Oct 7 12:23:18 2008] [16301] DBMS error code: 1
ORA-00001: unique constraint (SHALA.A600_PK) violated."

那么我们针对提到的STATE=91370进行修复

sdegdbrepair -o repair_table -d ORACLE10G -V STATE:91370 -u sde -s bigserver

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Enter DBMS User Password:
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.