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

Comments embedded.
On Aug 15, 2:01 am, andreik <spamme.andr..._at_gmail.com> wrote:
> 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]
>

What it also says is this:

"This does not mean this space can be released to the tablespace, it could be under the HWM. To find the freeable space, use the UNUSED_SPACE procedure ..." and it provides a script to provide this information.

You need to run the procedure listed in the note to determine IF you have any 'wasted space' you can free, something you haven't done. My educated guess is you don't.

> 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
> ----------
> 0
>
> FS1_BYTES
> ----------
> 0
>
> FS2_BLOCKS
> ----------
> 0
>
> FS2_BYTES
> ----------
> 0
>
> FS3_BLOCKS
> ----------
> 0
>
> FS3_BYTES
> ----------
> 0
>
> FS4_BLOCKS
> ----------
> 0
>
> FS4_BYTES
> ----------
> 0
>
> 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.

Most likely because they are below the High Water Mark (HWM).

> And this explains
> why nothing happens when I run the:
>
> >alter table <table name> modify lob(<lob column name>) (shrink space [cascade]);
>

No, what explains that behaviour is the fact that none of this 'wasted' space is below the tablespace HWM.

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

I've explained this above.

> 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...

Why should it? The number of freepools hasn't changed, the CHUNK size is the same as the LOB size so there is nothing to modify or rebuild.

Apparently English is not your primary language, and I don't have a problem with that. What I do take issue with is your 'picking and choosing' the parts you like from the Metalink document rather than reading it in its entirety and using ALL of the provided information and techniques before posting your question here.

The Metalink document explains the issue well; it's a shame you couldn't take all of the information it presented instead of selectively applying only that which YOU thought was relevant.

David Fitzjarrell Received on Wed Aug 15 2007 - 09:33:25 CDT