Altering a table in Oracle and renaming an st_geometry attribute is not updating its metadata

Last Published: April 25, 2020


The table's metadata in the sde.st_geometry_columns and sde.st_geometry_index tables are not updated when renaming an st_geometry attribute using the SQL ALTER TABLE command in Oracle.

SQL> ALTER TABLE roads RENAME COLUMN shp TO shape;Table altered.

If the metadata does not correctly represent the actual table, for example, the name of the st_geometry attribute, applications will not be able to work with the spatial attribute.


Oracle's AFTER ALTER ON DATABASE event trigger for the ora_is_alter_column() property is not correctly returning the name of the attribute that is being renamed.

The ArcSDE administrator, user SDE, owns a trigger named db_ev_alter_st_metadata which is fired after every ALTER command is executed in the database. The trigger uses event information about the ALTER operation to check if the table being altered contains an st_geometry attribute. If yes, the trigger checks the name of the attribute being altered in the command and updates the st_geometry_columns and st_geometry_index metadata accordingly.

Without Oracle properly reporting the name of the attribute being altered, the trigger cannot properly maintain the st_geometry metadata.


There is no workaround to the problem. Do not use the ALTER TABLE RENAME COLUMN command to rename an st_geometry attribute.

    Article ID:000010088

    • Legacy Products

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options

    Discover more on this topic