How To: Calculate the storage space used by a raster dataset or raster catalog in Oracle
Instructions provided contain an example method to determine the physical storage size used by a raster dataset or raster catalog block table when stored in Oracle.
In ArcSDE, the block table component of a raster dataset or raster catalog stores the pixels of each raster band, and subsequently, this table consumes almost all the physical space required to store a raster dataset or raster catalog in the database.
In Oracle, the BLOCK_DATA column of a raster's block table stores pixel binary data as data type BLOB.
BLOB data storage in Oracle consists of the BLOB column, LOB segment, and LOB index. When the recommended default in-row storage is enabled, if the binary data stored in the BLOCK_DATA BLOB column exceeds 3,964 bytes, then it is stored in the LOB segment. The LOB segment is referenced in the BLOCK_DATA column using the LOB locator. See the link in the Related Information section below for further information about BLOB storage in geodatabases in Oracle.
To determine the physical storage space a raster dataset, or raster catalog, is using in Oracle, it is necessary to not only consider the space used by the block table, but also its LOB segment.
The method below describes how to identify the raster dataset or raster catalog's block table, find the LOB segment associated with that block table BLOCK_DATA column, and then find the total storage size by querying user_segments.
- Obtain the rastercolumn_id for the raster object. In this example, a rastercolumn_id of 1 means a block table named SDE_BLK_1 resides in the schema of the Oracle user who owns the raster dataset named 'AERIAL10'.
SQL> select rastercolumn_id from sde.raster_columns where table_name = 'AERIAL10';
- As the owner of the raster dataset or raster catalog, run the following query to find the LOB segment associated with the block table.
SQL> select segment_name from user_lobs where table_name = 'SDE_BLK_1' and column_name = 'BLOCK_DATA';
- Once the name of the LOB segment has been identified, it is then possible to query user_segments to find its size. The below example query returns the size in MB of the block table and associated lob segment.
SQL> select sum(bytes/(1024*1024)) MB from user_segments where segment_name = 'SDE_BLK_1' OR segment_name = 'SYS_LOB0000071444C00005$$';