Oracle – Is Block Size for Database or Tablespace?

I got something finally,

After further reading the DOC and making some test:

1. Yes, by default, depending on the size of the row, some blobs will be stored in row and other out of row.

2. Yes, following this you should be able to determinate it:

   LOB values are stored out-of-line when any of the following situations apply:

   • If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage clause when you create the table.
   • If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.
   • If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.

   LOB values are stored inline when any of the following conditions apply:

   • When the size of the LOB stored in the given row is small, approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the
     default).
   • When the LOB value is NULL (regardless of the LOB storage properties for the column).

3. No, this is just the default option, when the blob is too big(arpox 3800) it will be stored out of row

your db_block_size is 8k and your db_16k_cache_size = 0.

Normally we don't build databases with mulitple block sizes because the optimizer does not take the different block sizes into account. If you still want to do this, start with something like

alter system set db_16k_cache_size = 16m;

and try again. It does not have much to do with the filesystem blocksize but aligning your database storage blocks sizes with the storage blocksizes is performance wise a smart thing to do.