Table size grows indefinitely after deleting and then inserting the same data I create a table and populate it with data:

create table tab
as
select 'value' || level val
from dual
connect by level &lt;= 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 &lt;= 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.
If you have access to My Oracle Support:

Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (Doc ID 1601805.1)

So, without just copy-pasting the contents from there, the above note states:


the space management algorithm prefers allocating more space over reusing already allocated but free space
this is not a bug, but a feature, this is how it was designed
you can use shrink to compact the segment, as you have already discovered