Knowledge Base - Technical Articles


Technical Article   HowTo:  Alter an ArcSDE row_id sequence in Oracle

Article ID: 31437
Software:  ArcSDE 9.1, 9.2, 9.3, 9.3.1
Platforms: N/A

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:

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. -show me-

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

• SELECT R39.NEXTVAL FROM DUAL

• 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. -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.

  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>’

  2. Alter the sequence by using the registration_id from the above query:

    ALTER SEQUENCE <owner>.r<registration_id> INCREMENT BY 1;
    

Related Information


Created: 7/25/2006
Last Modified: 5/3/2011

If you would like to post a comment, please login