Online data reorganization, or the ability to allow users full access to the database during data reorganizations, improves the overall database availability and reduces planned downtime. Oracle Database 10g includes many online data reorganization features such as creating indexes online, rebuilding indexes online, coalescing indexes online, and moving index-organized tables (IOTs) online.

Oracle's online table redefinition feature offers database administrators unprecedented flexibility to modify physical attributes of a table and transform both the data and structure of a table while allowing users full access to the database. This feature can also make the application upgrade process easier, safer and faster.

Oracle Database 10g includes the following online data reorganization enhancements:

• Online table redefinition enhancements

  • Easy cloning of indexes, grants, constraints, etc.     

  • Convert from LONG to LOB online

  • Allow unique index instead of primary key

• Change tables without recompiling stored procedures

  • Stored procedures can depend on the signature of a table instead of the table itself

• Online segment shrink

  • Return unused space within the blocks of a segment to the tablespace

Online Table 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 database provides a powerful tool to redefine tables online. This 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.

The online table redefinition feature improves data availability, database performance, response time and disk space utilization.

Online table redefinition allows administrators to:

• Modify the physical attributes or storage parameters of a table

• Move a heap table or IOT to a different tablespace

• Add support for parallel queries

• Add or drop partitioning support

• Recreate a heap table or IOT to reduce fragmentation

• Change a heap table to IOT and vice versa

• Add, drop, or rename columns in a table

• Transform data in a table

Furthermore, during an application upgrade, administrators can start the redefinition process, then periodically synchronize the interim table so that the interim table includes the latest changes to the original table.  This reduces the amount of time to complete the final redefinition step.  Administrators can also validate and use the data in the interim table before completing the redefinition process.  This is a very useful feature for administrators, who must ensure that the upgrade process goes smoothly.

Oracle online data reorganization can be accomplished using either Oracle Enterprise Manager (OEM) or SQL*Plus command line interface.  Figure 1 below shows a sample OEM Reorg Wizard view.

Figure 1              Sample OEM Reorg Wizard View