Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Space in LOB segment for VARCHAR2(32767) columns not freed

castorpJan 20 2016 — edited Jan 22 2016

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:

  1. How can I free the no longer needed space from that segment (this is the more pressing problem right now)
  2. 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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 19 2016
Added on Jan 20 2016
10 comments
1,864 views