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.