New Features for Administrators - Question 091

Oracle9i provides a database package called dbms_redefinition to perform an online rebuild of a table.

Which two steps are you recommended to do prior to issuing the dbms_redefinition_start_redef table procedure call? (Choose two).

A. Grant privileges on the interim table

B. Invoke the dbms_redefinition.can_redef_table procedure

C. Invoke the dbms_redefinition.sync_interim_table procedure

D. Create any triggers, indexes, or constraints on the interim table

E. Create an empty interim table with all the desired characteristics

Answer: B, E

Explanation:

Before the issuing the dbms_redefinition_start_redef table procedure call it’s recommended to invoke the dbms_redefinition.can_redef_table procedure and create an empty interim table with all the desired characteristics. Dbms_redefinition_start_redef procedure verifies that the table can be redefined online.

Incorrect Answers

A: You don’t need to grant privileges on the interim table.

C: It is recommended to periodically synchronize the interim table with the source one when a large amount of DML is executed on the table while the re-organization is taking place by calling the dbms_redefinition.sync_interim_table() procedure. But this step is not required.

D: You don’t need to create any triggers, indexes, or constraints on the interim table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 78-80 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53 Chapter 2: Oracle9i Architecture Changes