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.
Best Answer
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: