Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: LOB segment space usage

Re: LOB segment space usage

From: andreik <>
Date: Sat, 18 Aug 2007 13:06:35 -0000
Message-ID: <>

On Aug 18, 2:48 pm, "Jonathan Lewis" <> wrote:
> Pulling together various comments from
> your posts, here:
> A 2KB block allows 1,988 bytes of space for LOB
> storage - the remaining 60 bytes is the standard block
> overhead plus a few bytes of data specific to the LOB,
> such as the object number, LOB ID, and SCN. So if
> your character set is single byte then you can get 1,988
> characters per block.
> As the note you've quoted says, if you use a multibyte character
> set as your database character (or for the National Character
> Set if you use NCLOBs) the data is stored as a fixed two-byte
> width - so you can only store 994 characters per block.
> You don't have to worry too much about subtle details with
> block dumps to get some useful information: For example:
> create a block using rpad('x',2000) then dump the LOB blocks.
> If the clob has been stored as a single byte character set you will
> see lots of
> 78 78 78 78 78 78
> in the block.
> If it's been stored as the fixed two-byte set you will see
> 00 78 00 78 00 78 00 78 00 78
> The note you've quoted about:
> "This ensures that there is no storage loss of character data
> in a varying-width format"
> is not trying to say you don't lose space in the data segment,
> it's trying to say that you don't lose detail in the character data.
> For example, you don't end up with code that a character with
> an acute accent (say) and find that it comes back without it's
> accent.
> Your comment about multiplying by two is appropriate - for
> multibyte character sets - it's the price you have to pay if you
> need the character precision. Don't forget you also pay in
> whole CHUNKs - a single character LOB stored out of line
> takes up a whole CHUNK, so choose your chunk-size with
> extreme care.
> There's an little note on my blog about reviewing LOB storage,
> but I don't think it will tell you anything you don't know already:
> --
> Regards
> Jonathan Lewis

Thank you for your clear explanation. I have been thinking it all over again and I see where I was mislead now. I didn't know about the 60K block overhead. So now I know it. And now I get a full picture of what's going on inside lobs.

I have learned a lot new during this "investigation", so I'm quite happy about it now :) Thanks.

Received on Sat Aug 18 2007 - 08:06:35 CDT

Original text of this message