I create a table and populate it with data:

create table tab
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;


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.


2 Answers 2


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
  • Unfortunately, I don't have access to My Oracle Support. Is there a way I can control this behavior? The limitation of manual table shrink is that it cannot be used on compressed tables.
    – Adamantium
    Feb 24, 2020 at 8:31
  • I ended up with disabling compression and schedling shrinking space periodically.
    – Adamantium
    Feb 24, 2020 at 11:01

Rather than using Delete, use TRUNCATE TABLE command.

  • I considered that. Unfortunately, I need the delete and insert to execute in one transaction.
    – Adamantium
    Feb 24, 2020 at 14:17
  • Why are you creating the table each time? It seems that you are creating a table, then deleting it and reinserting the same data. Is that correct? Feb 24, 2020 at 16:16
  • I encountered this issue in a materialized view that was regularly refreshed (COMPLETE mode). After investigating I noticed that I can get the same effect with delete-then-insert on a regular table (materialized view must be doing that under the hood).
    – Adamantium
    Feb 24, 2020 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.