English

How To: 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

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.

    Related Information