What is Dbms_Redefinition? Oracle docs says that it is a package which provides interface for objects redefinition.
I want to understand what exactly does this "redefinition" means? How is this redefinition different from DDL and DML commands? And what does "Online" means?
Typical usecase of this package is to change a "normal" table to a partionized table or an Index-Organized-Table. "Online" means, this change can be done without any interruption of your application (under certain circumstances)
Typically when you want to make such change you have to create the new table and copy data from old table to the new table. Then you have to drop the old table (which let your application to stop) and rename the new table. During this procedure you may lose data if application continues to insert data into old table.
Definition, as in Data Definition Language (DDL), means to define database objects.
Redefinition, in this context, means to redefine database objects.
Prior to DBMS_REDEFINITION being available redefinition of a database object could, and still can, be done using a number of different strategies with DDL scripts. Using DDL scripts you will generally find that for a period of time the database object being redefined is not available to users, also known as offline. The longer the database object is offline, the less 'happy' the user community is.
The solution is to use DBMS_REDEFINITION which can perform the redefinition of the database object with no down time.
The key problem in database is sometimes you want change it structure or definition, while at the same time maintaining it as online accessible to user:
anyone can still insert/delete/update the tables, while you are modifying its structure.
Oracle provide DBMS_REDEFINITION package for this purpose:
As quoted from:
http://www.dba-oracle.com/t_dbms_redefinition_example.htm
Now with all that said, Oracle provides a robust and reliable package for performing many common online table level reorganizations - dbms_redefinition.
The different stored procedures in the DBMS_REDEFINITION have to be use following a procedure, which is described in detail in the above link.
For a summary of the procedure is listed here, for details please see the link above:
1. Verify that the table is a candidate for online redefinition
2. Create an interim table
3. Enable parallel DML operations
4. Start the redefinition process (and do not stop until step 9 is done)
5. Copy dependent objects
6. Check for any errors
7. Synchronize the interim table (optional)
8. Complete the redefinition
9. Drop the interim table
