Redefining Oracle objects : Changing database objects in real-time.

Chris Lawson and Roger Schrag of DatabaseSpecialists, Inc., write in their paper, "Don't Shut Down That Database! Use Oracle 9i Online Object Redefinition Instead" that:

"The concept of dynamic instance parameters (the init.ora file) allowed DBAs to adjust certain instance parameters such as sort_area_size without having to boot all users off the system and restart the instance.

Oracle 8i introduced online index rebuilds and online table moves. However, the ability to make substantial changes by redefining Oracle objects without restricting user access was still lacking.

With the first release of Oracle 9i, a new concept called "online redefinition" was introduced. By calling a supplied PL/SQL package called DBMS_REDEFINITION, it is now possible to perform many types of table maintenance without taking away read or write access from users.

By redefining Oracle objects online, you can move a table to a new tablespace, change its storage parameters, add columns, remove columns, rename columns, change data types, change index and constraint definitions... and the list goes on.""

The question is: How to do it?

The key to redefining Oracle objects online in Oracle 9i (and later versions) is a supplied PL/SQL package called DBMS_REDEFINITION.

DBMS_REDEFINITION is used to redefine table columns and column names. Tables that can not be redefined are:

• Tables that have materialized views and materialized view logs defined on them cannot be redefined online
• Tables that are materialized view container tables and AQ tables cannot be redefined online
• The overflow table of an IOT table cannot be redefined online

Procedures and the privileges defined in DBMS_REDEFINITION package:

DBMS_REDEFINITION : System privileges required for redefining Oracle objects:

GRANT create session TO uwclass;
GRANT create materialized view TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create view TO uwclass;

GRANT execute ON dbms_redefinition TO uwclass;

CAN_REDEF_TABLE: Determines if a given table can be redefined online:

dbms_redefinition.can_redef_table (
uname IN VARCHAR2,
tname IN VARCHAR2,
options_flag IN BINARY_INTEGER := 1);

exec dbms_redefinition('REORG','EMP', cons_use_pk);

If the procedure completes successfully without raising an exception, then the table is eligible for online redefinition.

If the table is not eligible, then the procedure will raise an exception describing the problem.

CREATE AN INTERIM TABLE:

In order to avoid interfering with the production table, the online redefinition process makes use of an interim or staging table.

Instead of redefining Oracle objects directly on the production table, the changes are made to the interim table and data is copied from the production table into the interim table.

At the end of the redefinition process when all data has been loaded into the interim table and you are satisfied with the results, the production table and interim table will be swapped.

Be sure to give the table the same primary key as the existing production table.

However, do not create any indexes or declare any constraints on the interim table other than the primary key. The interim table and its primary key should be created with the exact definition and storage characteristics that are desired in the final, redefined table.

If column definitions will be changed (such as column names or data types), the interim table should use the final column names and definitions.

If the redefined table is to be index-organized or partitioned, then the interim table should be created that way.

SYNC_INTERIM_TABLE:

Maintains synchronization between the original and interim table:

dbms_redefinition.sync_interim_table (
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- original table
int_table IN VARCHAR2); -- interim table

START_REDEF_TABLE:

Starts the redefinition process.

Once the interim table has been created, the next step is to link the production table to it and copy the data.

To do this we use the START_REDEF_TABLE procedure in the DBMS_REDEFINITION package.

When calling this procedure, we simply supply the schema name along with the names of the two tables. If changes are being made to column mapping as part of the redefinition, then it will be necessary to supply an additional parameter to explain the column mapping.

dbms_redefinition.start_redef_table (
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- table to redefine
int_table IN VARCHAR2, -- interim table
col_mapping IN VARCHAR2 := NULL, -- column mapping
options_flag IN BINARY_INTEGER := 1, -- redefinition type
orderby_cols IN VARCHAR2); -- col list and ASC/DESC

ADD CONSTRAINTS, INDEXES, TRIGGERS, AND GRANTS TO THE INTERIM TABLE:

All of the rows in the production table have been copied to the interim table.

It is now time to add any constraints, indexes, database triggers and grants to the interim table that you wish to be present on the production table at the conclusion of the process of redefining Oracle objects.

Note that any foreign keys that you declare on the interim table at this point should be created with the DISABLE keyword.

The foreign key constraints will be enabled later in the redefinition process. Actually, foreign keys can be quite tricky.

COPY_TABLE_DEPENDENTS:

Copies the dependant objects of the original table to the interim table:

dbms_redefinition.copy_table_dependents()

FINISH_REDEF_TABLE:

Registers a dependent object (index, trigger or constraint):

dbms_redefinition.finish_redef_table

REGISTER_DEPENDENT_OBJECT:

Completes the redefinition process:

dbms_redefinition.register_dependent_object(
uname IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- table to redefine
int_table IN VARCHAR2, -- interim table
dep_type IN PLS_INTEGER, -- type of dependent object
dep_owner IN VARCHAR2, -- owner of dependent object
dep_orig_name IN VARCHAR2, -- name of orig dependent object
dep_int_name IN VARCHAR2); -- name of interim dependent object

ABORT_REDEF_TABLE:

Cleans up errors from the redefinition process:

dbms_redefinition.abort_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

exec dbms_redefinition('REORG','EMP','INT_EMP');

Limitations when redefining Oracle objects:

• Not Fully Online
• Renames happen after Redefinition is complete
• Old table is dropped before Renames
• Enables disabled referencing constraints
• Does not preserve original state of constraints
• Does not handle invalid triggers in 9i
• Does not support LONG/LONG RAWs
• Does not support individual partitions/subpartitions
• Does not support “NoLogging”mode for interim table
• Cannot handle referential constraints to different schema

These limitations make the function highly risky, in my opinion.

Redefining Oracle objects: Error Handling:

• Recoverable Errors: These errors can be removed by either fixing the problem or by editing the script to restart from point of failure
• Unrecoverable Errors:
  

1. Run ABORT_REDEF_TABLE
2. Turn materialized view back into interim table
3. Manually drop interim objects
4. Restart Redefinition from the beginning

Part of the information in this article is quoted with permission from DatabaseSpecialists, Inc.

Return from Redefining Oracle objects to Oracle DBA