After we migrated to (12.1.0.2 we changed some of our CLOB columns to VARCHAR2(32767) to benefit from it's (allegedly) better performance when reading them.
This has been working fine so far, however the LOB segment associated with the column does not get cleaned up.
The total length of all values in that table is roughly 180MB - calculated using sum(vsize(column_name)).
The table has approximately 130000 rows.
However, the segment itself has now grown to over 20GB - calculated using select sum(bytes) from user_lobs where segment_name = '....';
The segment has been (automatically) created with "securefile = yes" by Oracle, so I would have assumed the space management to be more efficient than that.
I have two questions:
- How can I free the no longer needed space from that segment (this is the more pressing problem right now)
- And how can I control the space usage similar to using the RETENTION option when defining "normal" LOB segments (e.g. a CLOB column)
The column does get updated a lot.
Thanks