Oracle: Get the real size of a table with blobs

By | January 12, 2016

As we now in case of a table with BLOBs in the actual table row only a reference to the BLOB is stored.
In order to get the real table size of my table ‘MY_BLOB_TABLE’ including the refereed BLOBs the following query holds in Oracle:


SELECT sum( bytes)/1024/1024 size_in_MB
     FROM user_segments
       WHERE (segment_name = 'MY_BLOB_TABLE'
               OR segment_name in (
                                   SELECT segment_name FROM user_lobs
                                       WHERE table_name = 'MY_BLOB_TABLE'
                                   UNION
                                   SELECT index_name FROM user_lobs
                                       WHERE table_name = 'MY_BLOB_TABLE'
                                  ) 
             );
Advertisements

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.