Reclaiming Oracle Segment Space

In an everyday production environment, Oracle DBAs face many challenges in tasks such as segment space management. For example, tables that are often updated may have very fragmented data segments or many chained rows. This may significantly affect the overall DML performance issued against such tables or introduce space wastage problems.

Until Oracle10g, there was no way to reclaim wasted space and compact data segments online without affecting end users. Figure 17.5 shows a data segment schema of a table. The small squares indicate rows stored in the segment.

When end users insert rows into the table, Oracle fills empty blocks allocated to the segment. Over the course of time, some rows might be deleted from the table, and at some point in time, the same segment could be presented by the schema in Figure 17.6.

The logical term High Water Mark (HWM) represents the highest space occupied by the segment. In Figure 17.7, it is clear that Oracle does not reclaim free space below the High Water Mark for other segments of the database or the free space within data blocks. This is because Oracle reserves that free space for future row inserts and possible row growth after updates. This method of space management within data segments has two weak spots. The first is that Oracle must scan all blocks below the HWM when performing full-table scans even though most of blocks are empty. This approach might significantly increase response time of full-table scans on tables which experience high data modification activity. The second thing that must be taken into account is when Oracle inserts rows through DIRECT PATH method such as an APPEND hint that is used in the insert statements, it always places new rows in data blocks above the HWM. Thus, the space below the HWM might be wasted.

In Oracle releases prior 10g, space could be reclaimed using such methods as exporting a table to the dump file, dropping it, and reloading data into the new table; or using the ALTER TABLE MOVE statement to move the table to another tablespace. The down side is that these methods prevent users from accessing their data during the table reorganization process.  The online reorganization feature might also be used, but that process requires at least double space to perform the operation.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: