How To: 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.
 
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, refer to How To: Preserve Oracle pipes, such as ArcSDE row_id values, when shutting down an Oracle instance.

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:
CREATE SEQUENCE R<registration_id>
MAXVALUE 2147483647
MINVALUE 1 
INCREMENT BY 16 
START WITH 1
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.
 
To obtain the NEXTVAL from the sequence representing the table, use one of the following:
SELECT R39.NEXTVAL FROM DUAL
Or
INSERT INTO <owner>.<table_name> VALUES (r39.nextval, ...
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.
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.

  1. Identify the registration_id of the table:
SELECT registration_id, table_name
FROM sde.table_registry
WHERE table_name = '<TABLE_NAME>'
AND owner = '<OWNER>'
  1. Alter the sequence by using the registration_id from the above query:
ALTER SEQUENCE <owner>.r<registration_id> INCREMENT BY 1;