Do I need to reclaim space in Oracle when updating a varchar2 column?

Question

If I have an Oracle table with a VARCHAR2 column with length 4000, and I have inserted a 4000 character string into the table, and then update the row with a 1 character string, do I need to do anything to make the other 3999 characters of space available for reuse, or is it automatically available for reuse?

Answer 1

After the update, 3999 bytes of space (assuming that 1 character = 1 byte in your database character set) is freed up in the block in which the row resides. That space will be immediately available if other rows in that block need to expand in size or if other columns in that row need to expand in size. Of course, since most databases use 8k blocks and the largest block size is 32k, it is likely that there are relatively few rows in this particular block since the original row was so large.

Oracle also tracks how full blocks are and uses that information to make them available for subsequent insert operations. The mechanics of this depend on the type of tablespace (locally or dictionary managed), the segment space management policy (automatic or manual), and table-level parameters like pctused. At a high level, though, freeing up 4k of space within a single data block will almost certainly cause the data block to be made available for future insert operations (or for update operations that cause rows in other blocks to need to be migrated to a new block or chained across multiple blocks). So the space will almost certainly be available to be reused.