Re: Wasted space inside LOB and strange problems releasing it.

On Aug 15, 7:10 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.

> Because your CHUNK size is equal to your LOB segment size.

can you explain this? I really don't get your point. maybe you intended to say "chunk size is equal to the tablespace block size" ?
because in my case the LOB _segment_ is about 4G and it consists of 4K chunks.

You should not be so sure about the PCTUSED and PCTFREE parameters, since I'm using the ASSM tablespace and I believe that blocks are managed some other (was it a bitmap or smth?...) way there.

But anyway, I believe I have found something new here. I have made some tests.
I created a tablespace called "smallblock" with blocksize of 2KB.

Then created a table with a LOB segment (chunk is also 2K)

SCOTT_at_andrkydb> create table t1 (i int, b nclob) lob (b) store as t1_lob (chunk 2K disable storage in row tablespace smallblock);

SCOTT_at_andrkydb> select bytes from dba_segments where segment_name = 'T1_LOB';      BYTES

     65536

Then I wrote a simple script which inserted 1000 rows into table T1, putting a bit less than 2K of data into the "b" field (so that each inserted row would take exactly 1 chunk of the LOB segment)

After running the script:

SCOTT_at_andrkydb> select bytes from dba_segments where segment_name = 'T1_LOB';      BYTES

   5242880

So you see that my LOB has grown up to ~5M

But when I count the "real bytes" then I get a much smaller number:

SCOTT_at_andrkydb> select sum(dbms_lob.getlength(b)) from t1;

SUM(DBMS_LOB.GETLENGTH(B))

                   1952000

So.. I had 2K tablespace blocks and 2K LOB chunks. I was inserting about 2K of data in each row and got the LOB segment about 2x bigger than is needed by the data.

As I understand now, I will not get to shrink my LOB, because there are actually no "deleted" or "free" blocks, but space is being taken over by some spooky Oracle internal stuff... Now I wonder, if it could be explained what is that space is being used for?

ps. otherwise the metalink article works. meaning, that when I delete some stuff from the table and then run the dbms_space, I don't see any blocks being unused. but after I rebuild the freepools, the blocks really become marked as free and I can release them using the "alter table ... shrink space" command. I will not be posting the logs of that part of my tests but you can try it yourself to see that it doesn't have to do anything with LOB chunk being equal to anything. Received on Thu Aug 16 2007 - 02:30:09 CDT