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.

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

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'.

Article ID: 000009931

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Detect the storage usage of a st_geometry spatial index in Oracle

Summary

Procedure

Receive notifications and find solutions for new or common issues

Discover more on this topic