I create a table and populate it with data:
create table tab as select 'value' || level val from dual connect by level <= 10000;
If I then delete all rows and insert the same data:
delete from tab; insert into tab select 'value' || level val from dual connect by level <= 10000; commit;
The table size increases every time delete-then-insert is performed. Executing these operation multiple times causes the table to grow to many times it's original size.
I would expect the size to remain (approximately) the same. Why is this happening?
A delete marks space as free, it doesn't actually shrink the segment. A subsequent insert should first use available empty blocks below HWM.
I can recover the space using
alter table tab shrink space.
I tested in Oracle 12 and Oracle 18c.