With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.

Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?


You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...).

To get a total size in bytes of column for the entire table SUM it up like this:

SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes
FROM my_table

If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual size in bytes used. Compare the used_bytes value before/after your enable settings that could change the size of the BLOB column (eg. dedupe, compression, etc).

    p_schema_name           varchar(256) DEFAULT 'THE_SCHEMA_NAME';
    p_table_name            varchar(256) DEFAULT 'THE_TABLE_NAME';
    l_segment_name          varchar2(30);
    l_segment_size_blocks   number; 
    l_segment_size_bytes    number; 
    l_used_blocks           number;  
    l_used_bytes            number;  
    l_expired_blocks        number;  
    l_expired_bytes         number;  
    l_unexpired_blocks      number;  
    l_unexpired_bytes       number;  

    FOR rec IN (select segment_name 
                from dba_lobs 
                where owner = p_schema_name
                  and table_name = p_table_name
                order by 1)
        dbms_output.put_line('Segment Name=' || rec.segment_name);

            segment_owner           => p_schema_name,  
            segment_name            => rec.segment_name, 
            segment_type            => 'LOB', 
            partition_name          => NULL, 
            segment_size_blocks     => l_segment_size_blocks, 
            segment_size_bytes      => l_segment_size_bytes, 
            used_blocks             => l_used_blocks, 
            used_bytes              => l_used_bytes, 
            expired_blocks          => l_expired_blocks, 
            expired_bytes           => l_expired_bytes, 
            unexpired_blocks        => l_unexpired_blocks, 
            unexpired_bytes         => l_unexpired_bytes 

        dbms_output.put_line('  segment_size_blocks       => '||  l_segment_size_blocks);
        dbms_output.put_line('  segment_size_bytes        => '||  l_segment_size_bytes);
        dbms_output.put_line('  used_blocks               => '||  l_used_blocks);
        dbms_output.put_line('  used_bytes                => '||  l_used_bytes);
        dbms_output.put_line('  expired_blocks            => '||  l_expired_blocks);
        dbms_output.put_line('  expired_bytes             => '||  l_expired_bytes);
        dbms_output.put_line('  unexpired_blocks          => '||  l_unexpired_blocks);
        dbms_output.put_line('  unexpired_bytes           => '||  l_unexpired_bytes);

This code is a slightly modified version of the code in Oracle's post explaining how deduplication works. The original is available here: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html.

  • This gives me the total number of logical bytes in the blobs, not the actual footprint. If for example I have 10000 lobs, all of them identical and I've specified deduping in my storage parameters, it will only be stored once. Summing the bytes up this way would give me a result that is wrong by about a factor of 10000. – biziclop Aug 7 '13 at 10:59
  • @biziclop : the answer was edited. used_bytes will give you the space used for all lobs in the segment. If you study the article you will see that this value is smaller as expected if one uses deduplication and compression. – miracle173 Aug 11 '13 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.