Knowledge Base - Technical Articles

HowTo: Alter an ArcSDE row_id sequence in Oracle

Summary

Instructions provided explain how to alter an ArcSDE row_id sequence in Oracle. This prevents values from being lost when a non-ESRI application references the sequence.

Background

Geodatabase object classes require an ArcGIS maintained row_id attribute. The row_id value is created by using an Oracle sequence for each object class. -show me-

An Oracle sequence is a database object that provides unique integer values. Once a value is requested and consumed from the sequence generator, the value can never be returned. If multiple sessions access the sequence, there is a potential for gaps in the values returned to each session.

For example, User A requests the next value from the sequence and receives the value 13. User B requests the next value and 14 is returned. Finally, User A requests another value and the sequence returns 15. Therefore, User A received the unique but not sequential values 13 and 15.

When using ArcGIS to create a new row or feature, the geodatabase requests a block of 10 row_id values from ArcSDE. The geodatabase requests row_id values and creates new objects in blocks of ten, reducing the number of requests to the database.

To learn how to preserve Oracle pipes, i.e., ArcSDE row_id values, when shutting down an Oracle instance, see the link in the Related Information section below.

ArcSDE’s sequence naming convention is: R<#> where the value of '#' is the registration_id for the table in the sde.table_registry. Each sequence is created with the following properties:

For non-versioned editing, non-ESRI applications occasionally require the ability to insert rows into a registered ArcSDE table. In order for the non-ESRI application to provide a unique value for the row_id attribute, the application needs to reference or obtain the NEXTVAL from the sequence representing the table. -show me-

To obtain the NEXTVAL from the sequence representing the table, use one of the following:

By default, ArcSDE defines the sequence to increment the value by 16. Each time a non-ESRI application requests the next value from the sequence, the value increments by 16 and the previous 15 values are lost. Prevent the loss of 15 values each time a non-ESRI application references the sequence by altering the sequence to increment by a value of 1. -show me-

ALTER SEQUENCE R<registration_id>
INCREMENT BY 1

By changing the increment value to 1, each time the non-ESRI application requests the next value in the sequence it increments by a single value.

Changing the INCREMENT BY property does not change the geodatabase requests blocks of row_id values. The geodatabase still requests row_id values in blocks of 10. Changing the INCREMENT BY property satisfies the geodatabase's request by querying the sequence 10 times to obtain 10 values.

Procedure

Alter the row_id sequence INCREMENT BY property to 1. Each time the sequence is referenced, the next value increments by a value of 1.