English

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

Description

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.

Code:
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.

Cause

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.

Workaround

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