Wasted space inside LOB and strange problems releasing it.

From: andreik <spamme.andreik_at_gmail.com>
Date: Wed, 15 Aug 2007 07:01:45 -0000
Message-ID: <1187161305.108009.269740@57g2000hsv.googlegroups.com>

Hi all,

so my situation here:
10.2.0.3 with RAC (one node)
SunOS 5.9 sparc 64bit

There is a table with a NCLOB type field. The table and the LOB segments reside in different tablespaces. Block size is 4K, LOB chunk is also 4K
LOB resides in an ASSM tablespace, table in a non-ASSM tablespace.

There is a good metalink note (Note:386341.1) which talkes about how to determine wasted space inside LOBs and how to release it. According to it the following should be correct:

When I query the dba_segments view:

SQL> select bytes from dba_segments
where segment_name = 'SYS_LOB0000133926C00014$$'; --<== this is my LOB's name, got it from dba_lobs

BYTES

4690280448

But when I count the real bytes taken by the data:

SQL> select sum(dbms_lob.getlength(flob1)) from T1;

SUM(DBMS_LOB.GETLENGTH(VASTUS))

2288847401

So as I understand (and as the metalink says) I have 4690280448 - 2288847401 = 2401433047 bytes wasted.

The good metalink article says that:
[quote]
To turn the deleted space into free, you have to rebuild the freepools. The command used to do this is: alter table <table name> modify lob(<lob column name>) (freepools <number of free pools>);
[/quote]

I ran this command. Alert log gave notice:

Wed Aug 14 17:42:01 2007
LOB Freelists need to be dropped. This may take some time.

Now I want to see the blocks to become marked as "free". So I use the DBMS_SPACE.SPACE_USAGE procedure like this:

SQL>
begin
DBMS_SPACE.SPACE_USAGE(

   'XLKF',
   'SYS_LOB0000133926C00014$$',
   'LOB',


   :unformatted_blocks ,

   :unformatted_bytes  ,

   :fs1_blocks         ,

   :fs1_bytes          ,

   :fs2_blocks         ,

   :fs2_bytes          ,

   :fs3_blocks         ,

   :fs3_bytes          ,

   :fs4_blocks         ,

   :fs4_bytes          ,

   :full_blocks        ,

   :full_bytes,

	NULL

);
end;

SQL>print

UNFORMATTED_BLOCKS

5375

UNFORMATTED_BYTES

22016000

FS1_BLOCKS

FS1_BYTES

FS2_BLOCKS

FS2_BYTES

FS3_BLOCKS

FS3_BYTES

FS4_BLOCKS

FS4_BYTES

FULL_BLOCKS

1137624

FULL_BYTES

4659707904

As you can see all the FS* values are 0. This should mean that there are still no blocks to be released from the segment. And this explains why nothing happens when I run the:
>alter table <table name> modify lob(<lob column name>) (shrink space [cascade]);

So my question is: why the blocks are not getting marked as "free" and if there is another approach to the problem?

ps.
I must also notice that the LOB was in a non-ASSM tablespace before and I have moved it into an ASSM tablespace so to have the "alter table ... modify lob ... shrink space" feature, which is not available in a non-ASSM tablespace. I was also hoping that in the process of moving the LOB will get rebuilt but it wasn't... Received on Wed Aug 15 2007 - 02:01:45 CDT