Error: Sequence does not exist, Oracle error 02289

Error Message

This error occurs when editing an ArcSDE geodatabase and attempting to insert a new feature or update an existing feature's geometry:

"Create: An unexpected failure occurred.
Underlying DBMS error [ORA-02289: sequence does not exist] [<owner>.<feature class name>]"


ArcSDE 8.1 uses Oracle sequences to generate table row_id and layer shape values. The sequences are created when the table or layer is initially created, or when the ArcSDE instance is upgraded to release 8.1. The sequences are always owned and located in the schema of the user who owns the data source. They must be present to be able to create new features or geometries. If they are mistakenly dropped or lost during an Oracle export/import the aforementioned error will be raised.

The sequences are named R<registration_id>, where the registration_id value is equal to the table's unique registration identifier and I<layer_id>. For the latter, the layer_id value is equal to the layer's unique layer identifier.

In ArcSDE release 8.1, renaming a layer will mistakenly drop the I<#> sequence.

Solution or Workaround

When either the R<#> or I<#> sequence is not present, only the owner of the data source will be able to insert new features or update existing feature geometries.

The solution is to allow the owner of the data source to perform an insert or update a geometry. The user does not need to save the changes, the sequence will be successfully created.

ArcSDE detects if the sequence is not present and will create the required sequence. This process requires the user who performs the operation is the owner of the data source, because the sequence must be created in the schema of the data source's owner.