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 Gartner Group study, the cost of 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

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.
• 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 is accessible by all read and write operations during the redefinition process. 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. 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.

Depending on the type of online reorganization that is required, it can be performed using either the DBMS_REDEFINITION package or using the SQL CREATE/ALTER TABLE and INDEX commands as shown in the table below.