DBAs faced data fragmentation issues frequently. Data fragmentation affects the database performance.
In production database, we need to follow any one of the below method to solve the fragmentation issue.

Methods

1. Alter table move tablespace ;
— Also we need to rebuild the indexes associate with tables.
2. Export the table / truncate the table / import the table
3. Create table as select (CTAS)
4. Online Data Redefinition
Table is not accessible for during the reorganization of table using Method 1, 2 & 3.

Using online redefinition, we no need outage & also user can access the table for DML operation.

About online redefinition

For large, active databases, it is sometime necessary to redefine large hot tables to improve the performance of queries or data manipulation language (DML) operations performed against these tables. Additionally business applications may require underlying database structure to be changed or transformed periodically.
Oracle Online Redefinition mechanism provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline. When a table is redefined online, it is accessible by all read and write operations during the redefinition process. Administrators then have control over when to switch from the original to the newly redefined table.
The switch process is very brief and is independent of the size of the table or the complexity of the redefinition. The redefinition process effectively creates a new table and improves its data block layout efficiency.

Last week I have tested the table fragmentation scenario in my test server.

Operating system: Windows XP
Oracle version: 11.1.0.7

Steps for Online Redefinition of Tables

There are five basic steps to redefine a table:

1. Create a new image of the table with all of the desired attributes.
2. Start the redefinition process.
3. Create any triggers, indexes, grants and constraints on the new image of the table.
4. Optionally synchronize and validate data in the new image of the table periodically.
5. Complete the redefinition of the table.

With online table redefinition, you can

-Modify the physical attributes or storage parameters of a table.
-Convert a LONG or LONG RAW column to a LOB.
-Add or drop partitioning support.
-Add, drop, or rename columns in a table.
-Reorganize a Single Partition.

In our next article we will see how to Create a user & test the scenario.