How To: Alter an ArcSDE row_id sequence in Oracle
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.
BackgroundGeodatabase object classes require an ArcGIS maintained row_id attribute. The row_id value is created by using an Oracle sequence for each object class.
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
SELECT R39.NEXTVAL FROM DUAL
INSERT INTO <owner>.<table_name> VALUES (r39.nextval, ...
ALTER SEQUENCE R<registration_id> INCREMENT BY 1
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.
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.
- Identify the registration_id of the table:
SELECT registration_id, table_name FROM sde.table_registry WHERE table_name = '<TABLE_NAME>' AND owner = '<OWNER>'
- Alter the sequence by using the registration_id from the above query:
ALTER SEQUENCE <owner>.r<registration_id> INCREMENT BY 1;