LOB related tasks

Tables that use LOB objects usually grow quickly in size.

LOB object can be either inline or can be stored outside the table row. There are certain in built rules that will decide if an LOB object is stored inline or not. Refer to this Blog:

Query to view LOB data (inline) sizing in a table:

select

   table_name,round((blocks*8),2) "size (kb)" ,

   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",

   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"

from

   dba_tables

where

   table_name = 'table name'

  and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

/

If LOB segment is stored outside the table (there is a index in the table that points to the LOB segments)

use this to view the size :

select table_name,column_name,segment_name,a.bytes/1024/1024/1024 

from dba_segments a 

     join

     dba_lobs b using (owner, segment_name)

where b.table_name = 'TABLE_NAME';

You can also use advanced LOB compression to achieve 2x to 4x compression. You will need Advanced Compression Option license to do this.