Identify existing Oracle storage properties for the ST_Geometry points' attribute

Summary

It may be necessary to identify an ST_Geometry points' LOB storage properties in Oracle. If the LOB segment or index names were not defined when creating an ST_Geometry attribute, the names of these objects might be needed for administrative reasons.

For example, I/O performance issues on the LOB segment may occur when database monitoring. It may be necessary to move the LOB segment to a less active hardware device to improve read/write access. Moving the LOB segment requires knowing the segment's name. Find the segment's name by querying Oracle's USER_LOBS or DBA_LOBS views.

Procedure

Code:
SQL> DESCRIBE user_lobs
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------
 TABLE_NAME                                                     VARCHAR2(30)
 COLUMN_NAME                                                    VARCHAR2(4000)
 SEGMENT_NAME                                                   VARCHAR2(30)
 TABLESPACE_NAME                                                VARCHAR2(30)
 INDEX_NAME                                                     VARCHAR2(30)
 CHUNK                                                          NUMBER
 PCTVERSION                                                     NUMBER
 RETENTION                                                      NUMBER
 FREEPOOLS                                                      NUMBER
 CACHE                                                          VARCHAR2(10)
 LOGGING                                                        VARCHAR2(7)
 IN_ROW                                                         VARCHAR2(3)
 FORMAT                                                         VARCHAR2(15)
 PARTITIONED                                                    VARCHAR2(3)

Code:
SQL> SELECT segment_name, tablespace_name, index_name
  2  FROM user_lobs
  3  WHERE table_name = 'STREETS' AND column_name = 'SHAPE';

SEGMENT_NAME          TABLESPACE_NAME      INDEX_NAME
--------------------- -------------------- ------------------------
STREETS_SHAPE_SEG     USER_DATA            SYS_IL0000346894C00002$$

Note:
See Oracle's documentation for additional information.