Oracle Space Usage – Why Does Oracle Indicate My Table is Using So Little Space?

oracle

I'm running Oracle 11g. I have a table with two BLOB columns. I inserted 10,000 rows into the table, and each column contained a byte array of size 10289.

As you can see, there are 10,000 rows in the table and each column has 10,289 bytes in it:

SQL> select count(*) from MYUSER.MYTABLE;

  COUNT(*)
----------
     10000

SQL> select min(length(COL1)), min(length(COL2)) from MYUSER.MYTABLE;

MIN(LENGTH(COL1))      MIN(LENGTH(COL2))
-----------------      -----------------
            10289                  10289

Therefore the table should be using up at least 205MB of space (10289 bytes * 2 * 10000).

But I used the query given here to find out how much space the table is using, and I got the following:

SQL> SELECT SUM(bytes), SUM(bytes)/1024/1024 MB FROM dba_extents WHERE owner = 'MYUSER' AND segment_name = 'MYTABLE';

SUM(BYTES)         MB
---------- ----------
   2097152          2

Why does it say the table is using up just 2 MB when it should be using up 250 MB?

The byte arrays I'm inserting are compressed data, so Oracle couldn't possibly be compressing that data any further.

Best Answer

Your table's segment is only 2 MB. The rest of the data is stored in the LOB segments which are physically separate from the table segment.

SELECT column_name, segment_name
  FROM dba_lobs
 WHERE owner = 'MYUSER'
   AND table_name = 'MYTABLE'

should show you that there are two LOB segments associated with the table. You can then query dba_segments to get the size of those segments 

SELECT sum(bytes)/1024/1024 mb
  FROM dba_segments
 WHERE owner = 'MYUSER'
   AND segment_name IN (SELECT segment_name
                          FROM dba_lobs
                         WHERE owner = 'MYUSER'
                           AND table_name = 'MYTABLE')
Related Question