HOW TO

Rename the ST_Geometry points' Oracle LOB segment

Last Published: April 25, 2020

Summary

Instructions provided describe how to rename the ST_Geometry points' LOB segment.

If the LOB segment name is not specified during the creation of the ST_Geometry attribute, the LOB segment name is created automatically by Oracle. Without an identifiable name, it may be difficult to identify which LOB segments are impacted by performance. By providing a LOB segment name, the user controls the naming convention and can specify a name that relates to the table name.

Procedure



  1. Use Oracle's ALTER TABLE command the to rename a LOB segment.

    Code:
    SQL> ALTER TABLE streets
    2 MOVE LOB(shape.points) STORE AS streets_lob_seg;

    Table altered.



    When Oracle moves the physical storage of the LOB segment and table, all existing indexes are marked as unusable.

    Code:
    SQL> SELECT index_name, status
    2 FROM user_indexes
    3 WHERE table_name = 'STREETS';

    INDEX_NAME STATUS
    ------------------------------ --------
    STREET_NAME_IDX UNUSABLE
    R45_SDE_ROWID_UK UNUSABLE
    SYS_IL0000346092C00038$$ VALID
    A9_IX1 UNUSABLE



  2. Each index must be rebuilt so that it is valid. Enter the code below to alter the index:

    Code:
    SQL> ALTER INDEX street_name_idx REBUILD;

  3. Optional: Gather new table statistics.
    Note:
    See Oracle's documentation for additional information.


Article ID: 000009516

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options