Reorganizing the Database Whilst Users Are Changing Data

Oracle Online Redefinition was introduced in Oracle 9i and with each release of Oracle, it has been enhanced such that it offers a comprehensive range of features for reorganizing the database for both physical and database schema changes without requiring users to disconnect from the database.

During the lifetime of the database, it is inevitable that changes will be required to the database, because it is impossible for the designer to foresee, every possible data requirement. Also government regulations could change requiring that additional data is retained or the business may demand some structural changes to the databse design. Typical changes that are needed include, changing the storage requirements of a table, or the columns contents and types of data being stored.

How to perform an Online Redefinition ?
An Online Reorganization can be performed using either Oracle Enterprise Manager or from the command lines, using one of following three methods , which will depend upon the type of reorganization that is required:

• the package DBMS_REDEFINITION
• specify the ONLINE clause on certain SQL statements
• specify the SHRINK SPACE clause on certain SQL statements
  

The DBMS_REDEFINITION package
Most database reorganizations will be performed using the package DBMS_REDEFINITION, which allows the following changes can be made to the database:

• 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
• Convert a LONG or LONG RAW column to a LOB
• Transform data in a table
  

ONLINE clause in INDEX and TABLE statements
Some changes are possible using the ONLINE clause in the CREATE/ALTER INDEX and TABLE statement, which allows the following to be performed online:

• Create index
• Rebuild index
• Coalesce index
• Move table

Reclaiming Unused Space
A common problem in many databases is that overtime the available space is not being efficiently used. Originally all the data was probably packed well together, but as rows were deleted and data values changed, this would no longer be the case. Therefore, DBA's want to reclaim that valuable space, and now in Oracle Database 10g, this can be done online, for tables, indexes and materialized views, using the SHRINK SPACE clause.

How Easy is Online Redefinition to use?
When it comes to database reorganization, DBA's have preferred methods for performing this task. Some prefer to manually create scripts, which call the DBMS_REDEFINITION package or SQL statements directly. Whilst, others prefer to use Oracle Enterprise Manager, where a few simple questions are answered and it automatically generates a script and executes the redefinition. Therefore, online redefinition can be used by anyone from a novice to an experienced DBA.

Below is a simple example using the DBMS_REDEFINITION package where the table lilian is redefined to have 4 columns instead of 2

REM First check that table lilian can be redefined
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('lmh','lilian', dbms_redefinition.cons_use_rowid);
END;
/

REM Create an Interim Table
create table lilian_int
( a int,
b varchar2 (20),
c int,
d int );

REM Start the redefinition of table lilian, using interim table lilian_int
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('lmh', 'lilian','lilian_int', 'a a, b b, 0 c, 1 d', dbms_redefinition.cons_use_rowid);
END;
/

REM Now apply any changes to the table that have occurred whilst the redefinition
REM has been in progress. Note that you can sync multiple times.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('lmh', 'lilian', 'lilian_int');
END;
/

REM Finish the Redefinition of table lilian
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('lmh', 'lilian', 'lilian_int');
END;
/

The example shown here illustrates very basic functionality of what is possible with this powerful feature.

Summary
DBA's are beginning to discover Oracle's Online Reorganization capability. Unlike other tools, which concentrate on the physical side of database reorganization, the Oracle solution also includes the ability to perform data transformations and schema changes. Now, when database reorganization is required to improve database performance, it should be easier to schedule this task since it can be achieved without having to take the database offline.

References

1. Online Reorganization web site on OTN

Lilian Hobbs (Lilian Hobbs@Oracle.com) is a Senior Principal Product Manager with Oracle's High Availability Organization, where she is responsible for Online Redefinition, XA and Transactions and Information Lifecycle Management. During her long career in IT, she has personally reorganized many databases and knows what a daunting challenge it can seem the first time it is attempted.