English

How To: Rename the ST_Geometry points' Oracle LOB segment

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.


Related Information