Oracle Text LOBs - space allocation

Careful allocation of space for Oracle Text or other LOBs is extremely important, given that the storage unit is in chunks of Oracle blocks.
The CHUNK size is a multiple of the blocksize!

So you have chosen a reasonable blocksize of 8K for your default blocksize in the database,
what happens if your LOBs are much less than 8K? ... All that extra space is wasted per lob.

I noticed Oracle have released a good whitepaper on this issue which spells this out much better than the documentation and various scattered support notes. Pity this was too late (May 2004) for the people creating the database I now support :)

This database which has been severely burnt by the problem ala 50% space wastage of a large database.
We have both text and pictures, with the pictures stored out of the row. The LOB information is XML data. The average text size is 2K, the default blocksize is 8K. The average picture LOB XML is even less than 2K.
I ran a couple of scripts to determine the average lob space used and the amount of space wastage is averages 50-60%!!!

We have a couple of steps to complete before we can migrate to tablespaces which have smaller blocksizes (4k) and move the partitioned table lob data to those new tablespaces. In our case only the lob segments need to reside on the new (smaller blocksize) tablespaces.

I have included the script. The script gets back the distribution of lob sizes for a given table and lob name.
Note: The lob name is the column name which is of a LOB datatype.

It will also take a while to run this script on very large datasets or smaller spec machines.

Have Fun

--- script begins ---

-- Show sizes for LOBS
select avg(dbms_lob.getlength(&&lobname)) "average LOB size", stddev(dbms_lob.getlength(&&lobname)) "stddev LOB size", min(dbms_lob.getlength(&&lobname)) "Min LOB size",
max(dbms_lob.getlength(&&lobname)) "Max LOB size"
from table_name
/