By Karin Patenge, Sr. Principal Product Manager, Oracle Spatial and Graph Technologies, Oracle Germany

I would like to briefly offer a simple function to calculate the memory allocated for SDO_GEOMETRY tables. This takes into account only tables, LOB and index segments (this is not applicable for partitioned tables).

create or replace function alloc_space_in_mbytes (tablename in varchar2)
return number
is
  tablesize number;
begin
  select
    sum (bytes) into tablesize
  from (
    select
      segment_name as segment_name,
      bytes  as bytes
    from
      USER_SEGMENTS      -- Table segments            
    where 
      segment_name = tablename
    union all
    select
      s.segment_name as segment_name,
      s.bytes as bytes
    from
      user_lobs l,      -- Lob segments
      USER_SEGMENTS s
    where
      l.table_name = tablename and
      s.segment_name = l.segment_name
    union all
    select
      s.segment_name as segment_name,
      s.bytes as bytes
    from
      USER_INDEXES i,   -- index segments
      USER_SEGMENTS s
    where
      i.table_name = tablename and
      s.segment_name = i.index_name);


  tablesize := tablesize/1024/1024; -- Conversion in MB
  return tablesize;

end;
/


-- Call function for 1 table
select
  alloc_space_in_mbytes ('LIVELABS_ACCIDENTS') as "Allocated space in MB"
from dual
/
 
The call to the function can then look like this:
 
 
select
  alloc_space_in_mbytes ('GEOM_TABLE_UNTRIMMED') untrimmed, 
  alloc_space_in_mbytes ('GEOM_TABLE_TRIMMED') trimmed
from dual
/
A test table with 218 237 polygons, a total of 60,754,462 bases and existing spatial index result in the following values:
 
• SDO_ORDINATE_ARRAY values with predominantly 12 to 13 decimal places: 1643.5 MB
• SDO_ORDINATE_ARRAY values truncated to 5 decimal places: 1033.5 MB