Well, lob space is managed differently than normal table data is - when a block in a lob segment is freed, it does not immediately go onto the free list.

log segment space is managed by either the RETENTION clause or PCTVERSION. This is a quote from my last book on this topic:

<quote>

...
So, that gives us a reason to be concerned: if the undo segments are not used to store rollback for LOBs and LOBs support read consistency, how can we prevent the dreaded ORA-1555: snapshot too old error from occurring? And, as important, how do we control the amount of space used by these old versions? That is where RETENTION and, alternatively, PCTVERSION come into play.

RETENTION tells the database to retain modified LOB segment data in the LOB segment in accordance with your database’s UNDO_RETENTION setting. If you set your UNDO_RETENTION to 2 days – Oracle will attempt to not reuse LOB segment space ‘freed’ by a modification. That is, if you deleted all of your rows pointing to LOBS – Oracle would attempt to retain the data in the LOB segment (the deleted data) for two days in order to satisfy your UNDO_RETENTION policy – just as it would attempt to retain the undo information for the structured data (your relational rows and columns) in the UNDO tablespace for two days. It is important you understand that – the ‘freed space’ in the LOB segment will not be not be immediately reused by subsequent INSERTs or UPDATEs. This is a frequent cause of questions in the form of “my LOB segment is growing and growing”. A mass ‘purge’ followed by a reload of information will tend to cause the LOB segment to just grow – since the retention period has not yet expired.

Alternatively, the LOB storage clause could use PCTVERSION. PCTVERSION controls the percentage of allocated (used by LOBs at some point and blocks under the LOBSEGMENT’s HWM) LOB space that should be used for versioning of LOB data. The default of 10 percent is adequate for many uses since many times you only ever INSERT and retrieve LOBs (updating of LOBs is typically not done; LOBs tend to be inserted once and retrieved many times). Therefore, not much space, if any, needs to be set aside for LOB versioning.

However, if you have an application that does modify the LOBs frequently, the default of 10 percent may be too small if you frequently read LOBs at the same time some other session is modifying them. If you hit an ORA-22924 error while processing a LOB, the solution is not to increase the size of your undo tablespace, or increase the undo retention, or add more rollback segments if you are using manual undo management. Rather you should use the following:

ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );

and increase the amount of space to be used in that LOBSEGMENT for versioning of data.
....
</quote>


So, when you do your mass delete - you might have to wait quite a while before that free space "appears". If you use retention - you'll have to wait until that period of time passes. If you are using pctversion - then you just need to understand that N% of space will just be reserved for old lobs.


As to your test, there is insufficient data to conclude what you concluded. What I'll conclude instead is that the 200,000 existing rows were updated - but their lobs are still there. The new 100,000 rows are reusing other old space (I'll guess you are using pctversion).

Now that you understand how the free space management works - you'll be able to better forecast how space will be used.

I prefer retention myself for lob space management. It avoids the ora-1555 and will allow the entire lob segment to be used over time as it does not set asided a fixed percent of space to be used.