Search code examples
sqldatabaseoracleperformancedatastore

Query to get space used by oracle table is too slow

I am using below query to fetch the memory occupied by table.

SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
    FROM dba_extents
    WHERE owner = :Owner
    AND segment_name = :table_name;

It took 986 seconds (16 minutes) to print the size of 2 MB file.

What's wrong with this query?

Is there any better query which provides same data very fast?

Solution 
  • SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE owner = :Owner
AND segment_name = :table_name;

    There is far less segments than extents but it's strange. How many extents has your table?

    And just FYI this is just non-partitioned table data. And if the table has indexes, LOBs or object types it's not the entire space it requires.