Correct Oracle Tablespace Fragmentation

Yes, Oracle provides several techniques to address table fragmentation besides issuing a 'coalesce statement'. Some of these techniques include

1. Online Table Redefinition: Oracle offers the DBMS_REDEFINITION package to perform online table redefinition. This method allows you to redefine a table's structure while maintaining its data and keeping it available for DML and query operations. Online table redefinition is particularly useful for removing fragmentation while ensuring minimal downtime.
2. ALTER TABLE SHRINK SPACE: You can use the ALTER TABLE statement with the SHRINK SPACE clause to compact the table and release the freed space back to the tablespace. This operation can be performed online, allowing you to address fragmentation without affecting the table's availability.
   

   ALTER TABLE table_name SHRINK SPACE [CASCADE];
   

3. Export/Import (Data Pump): You can use Oracle Data Pump (expdp and impdp) to export the data from the fragmented table, then truncate or drop the table, and finally import the data back into a newly created table. This method can help to eliminate fragmentation and reduce the overall size of the table, but it requires downtime for the table during the process.
4. Reorganizing tablespaces: Another way to address fragmentation is by reorganizing the tablespace that contains the fragmented table. You can create a new tablespace, move the fragmented table to the new tablespace using the ALTER TABLE MOVE statement, and then drop the old tablespace. This method can help to reduce fragmentation at the tablespace level.
   

   ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
   

Keep in mind that each technique has its own advantages and drawbacks, and the best method depends on your specific situation and requirements. Always make sure to test these operations in a non-production environment and backup your data before making any changes to your production environment.

Question: What are the different ways to defragment a table?
I have a table with some free space after I executed a delete and wanted to know what options I have for defragmenting the table.
Answer: Fragmentation occurs as DML changes Oracle rows form their untouched state and intensive DML activity can cause full-table scans and index range scans to run too long, furthering the need to defragment the table to reduce block I/O. Oracle provides many methods for defragmenting a table, and any process that copies all of the table rows (i.e. create table as select) can be used to defragment a table:

1. Coalesce tablespace
2. Alter table tablename shrink space compact
3. Deallocate unused space: See these notes on the difference between coalesce and deallocate unused space
4. CTAS (or "alter table CUSTOMERS move"): This will defragment the table by copying the rows into their pristine state. You can also use dbms_redefinition to defragment an Oracle table.

Honeycomb fragmentation, in the context of Oracle table fragmentation, refers to a specific pattern of space fragmentation within a database object, such as a table or an index. This fragmentation occurs when the space within the object is not fully utilized, leading to the formation of gaps or "holes" in the data blocks that resemble a honeycomb structure.
Honeycomb fragmentation can arise due to various reasons, such as frequent updates, deletions, or insertions of rows in a table. These operations can cause the data within the table to become scattered, leaving empty spaces that are too small to accommodate new rows or updates efficiently. As a result, the overall performance of the database may be impacted, as additional I/O operations are required to access the fragmented data. To address honeycomb fragmentation in Oracle databases, several techniques can be employed, including:

1. Online Table Redefinition: By using the DBMS_REDEFINITION package, you can redefine the table structure online while maintaining its data and availability. This method can effectively remove honeycomb fragmentation with minimal downtime.
2. ALTER TABLE SHRINK SPACE: The ALTER TABLE statement with the SHRINK SPACE clause can be used to compact the table, filling the gaps caused by honeycomb fragmentation and releasing the freed space back to the tablespace.
   

   ALTER TABLE table_name SHRINK SPACE [CASCADE];
   

3. Export/Import (Data Pump): Oracle Data Pump can be used to export the data from the fragmented table, followed by truncating or dropping the table, and then importing the data back into a newly created table. This process helps eliminate honeycomb fragmentation and reduce the overall size of the table, albeit at the cost of some downtime.
4. Reorganizing tablespaces: By reorganizing the tablespace containing the fragmented table, you can address honeycomb fragmentation at the tablespace level. Create a new tablespace, move the fragmented table to the new tablespace using the ALTER TABLE MOVE statement, and then drop the old tablespace.
   

   ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
   

When dealing with honeycomb fragmentation, it is crucial to choose the appropriate technique based on your specific requirements and to test these operations in a non-production environment before applying them to your production database. To fix honeycomb fragmentation you need to combine adjacent free segments into one by issuing a coalesce statement

You can prevent tablespace fragmentation by specifying all the extents within a tablespace to be the same size. As you learned earlier, this standardization can be easily implemented by declaring storage parameters for the tablespace and not declaring storage parameters for the objects within the tablespace. This solution may seem like overkill to prevent an easily correctable problem. Sometimes it may be, since you may have good reasons, unrelated to extent size, for placing different objects in different tablespaces. Experienced researchers at Oracle have shown that there is not a great need for many different extent sizes. Some experts recommend only three different extent sizes; 160K, 160 MB and 4 GB. With fewer extent sizes, it is much easier to assign objects to a tablespace, as many of them will have the same extent size.

As of Oracle Database 10g, resolving fragmentation issues became fairly trivial. You can perform an online segment shrink using the Segment Advisor^[1] interface accessible through EM. ADDM recommends segments to shrink, and you simply choose to accept the recommendations. For Oracle9i databases, a common means of reducing fragmentation was through an online reorganization accomplished through a

CREATE TABLE...AS SELECT

online operation. That is, the copying of the contents of one table to another while the original table is updated. Changes to the original table were tracked and applied to the new table. Physical and logical attributes of the table could be changed during this online operation, thus allowing an online reorganization. Prior to Oracle9i, reducing fragmentation was more difficult. The general recommendation was to avoid fragmentation through careful planning. But the usual way to solve fragmentation was to reorganize a table by exporting the table, dropping it, and importing it. The data was unavailable while the table was in the process of being reorganized. Many DBAs claimed that they saw improved performance after reorganizing segments into a single extent. Over time, a decrease in performance reoccurred as the number of extents the table occupied increased. Oracle performance increased as a result of these reorganization operations, but this improvement was not due to a decrease in the number of extents. When a table is dropped and re-created, several things happened that increased performance:

1. Each block was loaded as full of rows as possible.
2. As a consequence, the high-water mark of the table (the highest block that has ever had data in it) was set to its lowest point.
3. All indexes on the table were rebuilt, which meant that the index blocks were as full as possible. The depth of the index, which determined the number of I/Os it takes to get to the leaf blocks or the index, was sometimes minimized.

By eliminating fragments and shrinking segments in a much more automated and online fashion, database releases since Oracle Database 10g greatly simplify solving fragmentation problems; the result is that optimal conditions exist for performance.
The next lesson is the wrap-up for this module.

[1] Segment Advisor:The Segment Advisor identifies segments that have space available for reclamation. It performs its analysis by examining usage and growth statistics in the Automatic Workload Repository (AWR), and by sampling the data in the segment.