How to remove ‘wasted rows’ after delete in Oracle SQL database

In Oracle sql database, a process in our system deleted (not truncated) approx 2 million rows from a table. This resulted in a huge number of ‘wasted rows’ causing the queries running on that table to take more than 9 hours which usually get over in 5 minutes. Upon checking, we found that the size of total number of actual rows was of around 2600MB whereas the overall table including ‘wasted rows’ had a size of 3700MB.

Please let me know what is the best way to delete rows and then get rid of ‘wasted rows’ so that we don’t have to rebuild the table every time.

Answer

Let’s simulate your case with a test table created with some data

create table tst as 
select 
rownum id, lpad('x',1000,'y') pad
from dual 
connect by level <= 100000;

The table consist of 15K blocks

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360 

If we delete all rows, the table size remains the same

delete from tst;
commit;

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
     15360 

After reorganising the table the table size goes down as the free space is removed.

alter table tst MOVE;  

select blocks from user_segments
where segment_name = 'TST';

    BLOCKS
----------
         8 

Note that this step requires a downtime of the application, no changes are allowed within the reorganisation.

Starting with Oracle 12.2 you can do this step ONLINE