HOW TO

Identify existing Oracle storage properties for the ST_Geometry points' attribute

Last Published: April 25, 2020

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

The USER_LOBS or DBA_LOBS view provides a LOB's storage properties, such as information about where the tablespace segment resides, the LOB segment's index name, the chunk size, or whether or not it is cached.
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)


Using the table name and ST_Geometry attribute name, query the view and discover its storage properties.
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$$


In the example above, the LOB segment name is STREETS_SHAPE_SEG, the LOB segment is stored in the USER_DATA tablespace, and the LOB segment's index name is SYS_IL0000346894C00002$$.

To modify these properties, execute the ALTER TABLE command.

Note:
See Oracle's documentation for additional information.

    Article ID:000009507

    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