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

On Aug 15, 5:33 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:

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

Thank you for you comment. English really is not my native language, so sorry for the mistakes.
I actually spent a whole day experimenting and reading that article over and over before posting here and I honestly considered the set of information I have provides to be sufficient. But ok, let's go deeper with this...

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

Most certainly I have tried that procedure also. It wasn't much of a help.
Also notice these words from metalink article: [Determine the unused space within the LOB segment, above the HWM. using the UNUSED_SPACE procedure.]
And as you know, we are not interested in the "above HWM" but need to reclaim the "deleted" blocks which must be "below HWM", am I right? Another quote:
[The deleted space can be converted into free by rebuilding the freelist or by rebuilding the LOB segment itself, but this is not always possible.]

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

another quote from the same article:
[However, the deleted space can be turned into free space and, when this happens, the procedure in 2.2 would show this free space. 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>);]

So that is why I was rebuilding the freepools.

So to sum up, here is my general understanding of the question: 1. when there are some blocks which are in the "deleted" state, they could be turned into "free" blocks by rebuilding the freepools. 2. none of the DBMS_SPACE package procedures can show you the blocks of the "deleted" state
3. after you rebuild the freelpools you can see blocks being freed. and you can then shrink the LOB segment using the alter table .. shrink space command.

My problem is:
1. after I run the freepools rebuild, I don't get any of the "deleted" blocks converted into "free" ones.

So why could such thing happen, when it is clearly seen that almost half of the LOB segment disk space is not being used by real data?... Received on Wed Aug 15 2007 - 10:41:26 CDT