English

How To: Detect the storage usage of a st_geometry spatial index in Oracle

Summary

Instructions provided describe how to discover how much storage is being consumed by an st_geometry spatial index, by querying an Oracle table using the spatial index's name.

Procedure

The Oracle table, user_segments, contains storage properties of each object in the user's schema. The attributes in the user_segments table report information about what tablespace the segment resides in, the number of bytes being consumed, the number of blocks and extents, the size of the initial and next extents, etc.

To detect the tablespace name, the number of bytes and blocks for a specific st_geometry table and index, follow the steps below.

  1. Discover the table's st_geometry unique identifier by querying the sde.st_geometry_columns table.
    Code:
    SQL> SELECT geom_id
    2 FROM sde.st_geometry_columns
    3 WHERE owner = USER
    4 AND table_name = 'TAXLOTS';

    GEOM_ID
    ----------
    32


  2. Use the geom_id value returned from step 1 as input to the next query.
    Code:
    SQL> SELECT tablespace_name, blocks, bytes, extents
    2 FROM user_segments
    3 WHERE segment_name = 'S32$_IX1';

    TABLESPACE_NAME BLOCKS BYTES EXTENTS
    --------------- ---------- ---------- ----------
    USERS 10096 82706432 631

    Note:
    The geom_id value from step 1 is embedded in the segment_name as 'S<geom_id>$_IX1'.