English

How To: Update ST_Geometry metadata when renaming tables and indexes in Oracle

Summary

If a table is renamed in Oracle which contains an ST_Geometry attribute or a spatial index is renamed, it is also required to update the ST_Geometry metadata by using the sde.spx_util.rename_spatial_table and sde.spx_util.rename_spatial_index procedures.

An ST_Geometry attribute contains metadata in the SDE.ST_GEOMETRY_COLUMNS table and a ST_Geometry spatial index contains metadata in the SDE.ST_GEOMETRY_INDEX table. If the table or index is renamed, then the metadata must be updated to reflect the new names.

Only the owner of the table or the SDE administrator can update the metadata or an error is encountered.

Procedure

The following examples demonstrate the necessary steps to update the ST_Geometry metadata when a table and index are renamed:

  • To update the metadata after renaming a table, execute the sde.spx_util.rename_spatial_table procedure. The procedure requires three input arguments, the table's owner, the table's previos name, that is, the name prior to the table being renamed, and the new table name.

    This example demonstrates how to use the Oracle RENAME command, renaming the parcels table to parcels_backup. Next, it demonstrates using the sde.spx_util.rename_spatial_table procedure, passing the owner of the table admin, the previous name of the table parcels and the new name of the table parcels_backup.

    Code:
    SQL> RENAME parcels TO parcels_backup;

    Table renamed.

    SQL> execute sde.spx_util.rename_spatial_table('admin','parcels','parcels_backup');

    PL/SQL procedure successfully completed.

  • To update the metadata after renaming an index, execute the sde.spx_util.rename_spatial_index procedure. The procedure requires four input arguments, the table's owner, the table's name, the name of the ST_Geometry attribute and the new index name.

    This example demonstrates how to ALTER an index name, renaming the a450_ix1 index to shape_idx. Next, it demonstrates using the sde.spx_util.rename_spatial_index procedure, passing the owner of the index admin, the table name parcels, the ST_Geometry attribute shape and the new index name shape_idx.

    Code:
    SQL> ALTER INDEX a450_ix1 RENAME TO shape_idx;

    Index renamed.

    SQL> execute sde.spx_util.rename_spatial_index('admin','parcels','shape','shape_idx');

    PL/SQL procedure successfully completed.