Re: LOB space, with numbers

"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message news:pan.2012.05.13.07.37.51_at_gmail.com...
| On Sun, 13 May 2012 01:58:57 +0000, Mladen Gogala wrote:
|
| The real problem is revealed by investigating with dbms_space. Even after
| deletion, DBMS_SPACE did not show any partially filled or empty blocks,
| only full blocks and unformatted blocks. That means that delete doesn't
| free blocks.
|
| FS1 Blocks = 0
| FS2 Blocks = 0
| FS3 Blocks = 0
| FS4 Blocks = 0
| Full Blocks = 73562
| Unformatted Blocks = 9160
|

 | The block numbers before and after deletion are exactly the same. Either
| DBMS_SPACE is buggy or space management within LOB segment is buggy.
| Judging by the huge disk space consumption, the latter is the case.

There is another interpretation - Oracle doesn't use the bitmap space managed blocks for LOB segments in ASSM, beyond marking them as FULL when they are first used. The LOBINDEX is (used to be) a two-part index, with one part showing the lob chunks which were available for re-use, keyed by the SCN at which the chunks were marked as deleted. When the chunk is re-used the index entry is deleted.

I think you need to test something like:

    set your auto_undo_retention to a very low value (say 30 seconds)     load your lobs into the table
    delete a large number of rows
    wait for a while (at least the undo_retention_time) - do a busy bit of work on some other table in the database

    insert the deleted lobs and see what happens to the space

There's a big difference between freelist management and ASSM when you look at the lob segment headers, by the way, so you may also see some variation in how things work if you switch.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543