How To: Rename the ST_Geometry points' Oracle LOB segment
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.
- Use Oracle's ALTER TABLE command the to rename a LOB segment.
SQL> ALTER TABLE streets
2 MOVE LOB(shape.points) STORE AS streets_lob_seg;
When Oracle moves the physical storage of the LOB segment and table, all existing indexes are marked as unusable.
SQL> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = 'STREETS';
- Each index must be rebuilt so that it is valid. Enter the code below to alter the index:
SQL> ALTER INDEX street_name_idx REBUILD;
- Optional: Gather new table statistics.
See Oracle's documentation for additional information.