Error Message
Attempts to create a new feature class or table in an ArcSDE geodatabase may result in this error:
"Failed to create feature class. Table already registered[Error executing stored procedure sde.registry_util.insert_registration::ORA-20218:Registration <id> already exists.][<username>.<feature class name>]"
"Failed to create feature class. Table already registered[Error executing stored procedure sde.registry_util.insert_registration::ORA-20218:Registration <id> already exists.][<username>.<feature class name>]"
Cause
Each feature class or table that is registered with ArcSDE is assigned a unique value in the REGISTRATION_ID column of the SDE.TABLE_REGISTRY table. This column has a primary key constraint that prevents the same ID from being applied to multiple tables or feature classes. In rare circumstances, ArcSDE may assign IDs that are already taken. This is the immediate cause of the error.
Solution or Workaround
The values for the REGISTRATION_ID column of the SDE.TABLE_REGISTRY table are generated by a sequence. The solution is to manually advance the sequence to a value that is higher than the current maximum value in the column.
- Connect to the database as the owner of the ArcSDE repository tables.
Determine the next value to be supplied by the sequence:
SQL>SELECT last_number FROM user_sequences WHERE sequence_name = 'TABLE_ID_GENERATOR';
Result for this example:
LAST_NUMBER
---------------
48 - Determine the maximum value in the ID column of GDB_OBJECTCLASSES:
SQL>SELECT MAX(registration_id) FROM TABLE_REGISTRY;
Result for this example:
MAX(ID)
------------
49
In this example, The next ID that is provided by the sequence is 48. The ID is generated by the sequence increment by adding one. Since the highest ID in TABLE_REGISTRY is currently 49 presume that 48 is already taken. Therefore ArcSDE assigns an existing ID to any new feature classes or tables. This violates the unique constraint on the REGISTRATION_ID column, resulting in the error. - In this example, the sequence needs to be advanced by two. Therefore we run the following SQL statement twice:
SQL>SELECT TABLE_ID_GENERATOR.NEXTVAL from dual;
Result for this example:
NEXTVAL
----------
48
SQL>SELECT TABLE_ID_GENERATOR.NEXTVAL from dual;
NEXTVAL
----------
49 - Confirm that the sequence has advanced by two by running the SQL statement from step 1 again:
SQL>SELECT last_number FROM user_sequences WHERE sequence_name = 'TABLE_ID_GENERATOR';
Result for this example:
LAST_NUMBER
---------------
50
The ID to be assigned to the next table or feature class in this example is now 50. Since this is higher than the current maximum REGISTRATION_ID value in TABLE_REGISTRY, there is no longer any possibility that the unique constraint will be violated.
Create new feature classes and tables in ArcSDE.
Related Information
- ORA-00001: unique constraint (SDE.GDB_OC_UC) violated
When creating a new feature class or table in an ArcSDE geodatabase, the following error message may occur: "ORA-00001: unique constraint (SDE.GDB_OC_UC) violated" - Underlying DBMS error (ORA-00001: Unique constraint (SDE.GDB_OC_PKC) violated)
The following actions may result in the error message: "Underlying DBMS error (ORA-00001: Unique constraint (SDE.GDB_OC_PKC) violated)". -Registering an SDE layer as versioned. -Registering an SDE layer with the geodatabase. -Registering a...
Created: 11/5/2003
Last Modified: 8/5/2009