"Mladen Gogala" <gogala.mladen@gmail.com> wrote in message
newsan.2012.05.13.07.37.51@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