Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

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.


Figure 17.5: The schema of table segment with stored rows within data blocks.


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.


Figure 17.6: After some rows have been deleted, the data segment wastes the space and HWM remains the same.


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:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.