In highly available systems, it is occasionally necessary to redefine large "hot" tables to improve the performance of queries or DML performed against these tables. The database provide a mechanism to redefine tables online. This mechanism provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline.
When a table is redefined online, it is accessible to DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window which is independent of the size of the table and the complexity of the redefinition.
This section contains the following topics:
Features of Online Table Redefinition
Online table redefinition enables you to:
The DBMS _REDEFINITION Package
The mechanism for performing online redefinition is the PL/SQL package DBMS _ REDEFINITION. Execute privileges on this package is granted to EXECUTE_ CATALOG _ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:
Steps for Online Redefinition of Tables
In order to perform an online redefinition of a table the user must perform the following steps.
It is possible to perform table redefinition in parallel. If you specify a degree of parallelism on both of the tables and you ensure that parallel execution is enabled for the session, the database will use parallel execution whenever possible to perform the redefinition. You can use the PARALLEL clause of the ALTER SESSION statement to enable parallel execution.
If the column mapping information is not supplied, then it is assumed that all the columns (with their names unchanged) are to be included in the interim table. If the column mapping is supplied, then only those columns specified explicitly in the column mapping are considered. If the method of redefinition is not specified, then the default method of redefinition using primary keys is assumed.
You can optionally specify the ORDERBY _COLS parameter to specify how rows should be ordered during the initial instantiation of the interim table.
Use the COPY _TABLE _DEPENDENTS procedure to automatically create dependent objects such as triggers, indexes, grants, and constraints on the interim table. This procedure also registers the dependent objects. Registering the dependent objects enables the identities of these objects and their cloned counterparts to be automatically swapped later as part of the redefinition completion process. The result is that when the redefinition is completed, the names of the dependent objects will be the same as the names of the original dependent objects.
You can discover if errors occurred while copying dependent objects by checking the NUM_ERRORS output variable. If the IGNORE_ERRORS parameter is set to TRUE, the COPY _TABLE _DEPENDENTS procedure continues cloning dependent objects even if an error is encounter when creating an object. The errors can later be viewed by querying the DBA _REDIFINITION _ERRORS view. Reasons for errors include a lack of system resources or a change in the logical structure of the table.
If IGNORE_ERRORS is set to FALSE, the COPY _TABLE _DEPENDENTS procedure stops cloning objects as soon as any error is encountered.
After you correct any errors you can attempt again to clone the failing object or objects by reexecuting the COPY _TABLE _DEPENDENTS procedure. Optionally you can create the objects manually and then register them as explained in method 2.
The COPY _TABLE _DEPENDENTS procedure can be used multiple times as necessary. If an object has already been successfully cloned, it will ignore the operation.
You can manually create dependent objects on the interim table. Use the REGISTER _DEPENDENT _OBJECT procedure after you create dependent objects manually. You can also use the COPY _TABLE _ DEPENDENTS procedure to do the registration. Note that the COPY _TABLE _DEPENDENTS procedure does not clone objects that are registered manually. You would also use the REGISTER _DEPENDENT _OBJECT procedure if the COPY _TABLE _DEPENDENTS procedure failed to copy a dependent object and manual intervention is required.
You can query the DBA _REDEFINITION _OBJECTS view to determine which dependent objects are registered. This view shows dependent objects that were registered explicitly with the REGISTER _DEPENDENT _OBJECT procedure or implicitly with the COPY _TABLE _DEPENDENTS procedure. Only current information is shown in the view.
The UNREGISTER_DEPENDENT_OBJECT procedure can be used to unregister a dependent object on the table being redefined and on the interim table.
The following is the end result of the redefinition process:
After the redefinition process has been started by calling START _REDEF _TABLE() and before FINISH _REDEF _TABLE() has been called, it is possible that a large number of DML statements have been executed on the original table. If you know this is the case, it is recommended that you periodically synchronize the interim table with the original table. This is done by calling the DBMS _REDEFINITION.SYNC _INTERIM _TABLE() procedure. Calling this procedure reduces the time taken by FINISH _REDEF _TABLE() to complete the redefinition process.
The small amount of time that the original table is locked during FINISH _REDEF _TABLE() is independent of whether SYNC _INTERIM _TABLE() has been called.
Terminate and Clean Up After Errors
In the event that an error is raised during the redefinition process, or if you choose to terminate the redefinition process, call DBMS _REDEFINITION.ABORT _REDEF_TABLE(). This procedure drops temporary logs and tables associated with the redefinition process. After this procedure is called, you can drop the interim table and its associated objects.
Example of Online Table Redefinition
This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:
The steps in this redefinition are illustrated below.
The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int _admin _emp table.
Restrictions for Online Redefinition of Tables
The following restrictions apply to the online redefinition of tables:
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.