I had inherited an Oracle database at work which Operations uses for different tasks. The database grows by thousands of records everyday from collecting network data. One day the system stopped working. I had performed an investigation and discovered there was no more space available in the tablespace because several tables were full of network data. The only choice was to delete the old records. After deleting records, I realized the tables’ sizes were not shrinking. So I did a Google search online to find out why. I found there are three extra steps which are needed to be performed after deleting records. Here they are:

1. ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;
2. ALTER TABLE MYTABLE SHRINK SPACE;
3. ALTER TABLE MYTABLE SHRINK SPACE CASCADE;

That’s it. That is what you need to do to keep a healthy and tidy Oracle database.