Reclaiming Wasted Space in Oracle

Back to: Oracle DBA Tutorials

Reclaiming Wasted Space in Oracle with Examples

In this article, I will explain how to reclaim wasted Space in Oracle with Examples. Please read our previous article discussing DBMS_SERVER_ALERT in Oracle with Examples. There is wasted space in the storage. Reclaim the wasted space and reap the benefits.

Over time, updates and deletes on the objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

To make the situation clearer, let’s assume that there is a data block, and in the data block, there is one row. When we update the row or reduce the size of the data block. This will lead to empty space. After a lot of updates and deletes, there will be many empty spaces from the empty blocks.

Objects with fragmented free space can result in much-wasted space and impact database performance. Oracle needs to scan all the free spaces, which leads to database performance degradation. The preferred way to defragment and reclaim this space is to perform an online segment shrink.

Oracle recommends we perform shrink in two steps.

Step 1: ALTER TABLE EMPLOYEES SHRINK SPACE COMPACT;

Step 2: ALTER TABLE EMPLOYEES SHRINK SPACE;

From the above image, there are contiguous blocks from block 1 to block 7 in the first segment. The segment belongs to the table employees. The first block and the second block are full. The blocks 3,4,5 are not completely full but have little space. The block 6 has HWM (HIGH WATER MARK). If we want to insert new data, then the data will be inserted after the HWM. This indicates we will not get the benefits of the previous data blocks with the few empty spaces.

From Step 1, if we execute the command “ALTER TABLE EMPLOYEES SHRINK SPACE COMPACT,” then the data will be moved from Block 4 and Block 5 to Block 3. This step will not adjust the watermark. The first step needs to be done during working hours because the DML operation and queries can be issued during the compaction.

During non-business hours, when the employees are not working, if we issue step 2, “ALTER TABLE EMPLOYEES SHRINK SPACE,” then the HWM will be adjusted.

Note: Because a shrink operation may cause ROWIDs to change in heap=organized segments, you must enable row movement on the corresponding segment before executing a shrink operation on that segment. Row movement is disabled by default at the segment level.

In the next article, I will discuss Segment Advisor in Oracle with Examples. In this article, I explain Reclaiming Wasted Space in Oracle with Examples. I hope you enjoy this article on Reclaiming Wasted Space in Oracle.