Bug: Failed to compress the database. Underlying DBMS error[SDE.DEFAULT]
Note: This issue is addressed in ArcGIS 9.1 Service Pack 1 (SP1).
Errors occur with ArcSDE 9.1 for Oracle when using the Compress Database command in ArcCatalog:
"Failed to compress the database. Underlying DBMS error[SDE.DEFAULT]"
The following error is displayed when using the 'sdeversion -o compress' command:
"Error: Underlying DBMS error (-51). Error: Unable to compress state tree. ORA-00907: missing right parenthesis"
The error occurs when there are greater than 150 states to be trimmed to base.
Note: This issue is addressed in ArcGIS 9.1 Service Pack 1 (SP1) Click the link in Related Information below to download the Service Pack.
It is advisable to have a full backup of the database before following this procedure in case it becomes necessary to undo the changes resulting from it. After following this procedure, compress will perform normally until compress needs to trim more than 150 states to base.
Disconnect all users from ArcSDE and restart the ArcSDE service.
Create a new child version of default and name it 'state_0'.
Connect to the database via SQLPlus as the SDE user and run the following command:
Code: UPDATE versions SET state_id = 0 WHERE name = 'state_0'
Compress the database.
Delete the version 'state_0'.
Compress the database again.
Check the SDE.VERSIONS table to make sure the state-id for DEFAULT has returned to zero.