Online Data Reorganization and Redefinition

The Online Reorganization & Redefinition feature in Oracle Database, offers administrators unprecedented flexibility to modify table physical attributes and transform both data and table structure while allowing users full access to the database.

Topic	Description
Overview	As businesses become more web- and e-commerce-enabled, high availability computing solutions are becoming increasingly important because of the extremely high cost of application downtime. According to a Gartner Group study, the cost of an outage for corporations involved in e-commerce can easily reach millions of dollars per hour. Therefore, database availability and manageability have become increasingly important in many of today's mission-critical environments. One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation.
Key Benefits	Modify table physical attributes and transform both data and table structure while allowing users full access to the database. Improve data availability, query performance, response time, and disk space utilization, all of which are important in a mission-critical environment Make the application upgrade process easier, safer, and faster. Execute using Enterprise Manager or SQL*Plus command-line interface.
How it Works	When a table is redefined online, it remains available for read and write operations throughout the redefinition process. During an application upgrade, administrators can begin the redefinition and periodically synchronize the interim table with changes made to the original table, reducing the time required for the final redefinition step. They can also validate and use the data in the interim table before completing the process, helping ensure that the upgrade proceeds smoothly. Administrators retain control over when to switch from the original table to the newly redefined table, and the switch is brief regardless of the table’s size or the complexity of the redefinition. Because the process effectively creates a new table, it can also improve data block layout efficiency. Depending on the type of online reorganization required, administrators can use either the `DBMS_REDEFINITION` package or SQL `CREATE TABLE`, `ALTER TABLE`, and `CREATE INDEX` statements.