Redefining Oracle objects : Changing database objects in real-time.Redefining Oracle objects : Background:
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:
Procedures and the privileges defined in DBMS_REDEFINITION package:
DBMS_REDEFINITION : System privileges required for redefining Oracle objects:
GRANT create session TO uwclass;
CAN_REDEF_TABLE: Determines if a given table can be redefined online:
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.
Maintains synchronization between the original and interim 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.
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.
Copies the dependant objects of the original table to the interim table:
Registers a dependent object (index, trigger or constraint):
Completes the redefinition process:
Cleans up errors from the redefinition process:
Limitations when redefining Oracle objects:
These limitations make the function highly risky, in my opinion.
Redefining Oracle objects: Error Handling:
Part of the information in this article is quoted with permission from DatabaseSpecialists, Inc.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.