Reorganization with the Redefinition Package


BRSPACE performs online table reorganization using the Oracle package DBMS_REDEFINITION. For more information on this package, see the Oracle documentation.


For each table to be reorganized:


       1.      BRSPACE calls the package to check whether the table can be redefined online. If a table cannot be processed – for example, if it has LONG or LONG RAW fields – the package returns an error and BRSPACE continues processing, excluding the table from the reorganization.

       2.      BRSPACE generates Data Definition Language (DDL) statements for the table.

       3.      BRSPACE creates an empty interim table. BRSPACE names the interim table <ORIGINAL_NAME>#.$

       4.      BRSPACE creates a primary key constraint for tables without a primary key but with a unique index, as recommended by Oracle.

       5.      BRSPACE calls DBMS_REDEFINITION to start the redefinition process to copy data from the original table to the interim table. This processing occurs in parallel if you set the required option.

       6.      BRSPACE creates all indexes, constraints, grants, triggers, and comments from the original table on the interim table. Indexes, constraints, and triggers are created with interim names, <ORIGINAL_NAME>#.$

When the redefinition is finished, the interim table has all the data and attributes of the original table.

       7.      BRSPACE calls RDBMS_REDEFINITION to finish the reorganization. RDBMS_REDEFINITION briefly locks both the original and interim table before renaming the interim table so that it has the same name as the original table.

       8.      If all the above steps have completed successfully, BRSPACE drops the original table and renames the indexes, constraints, and triggers back to their original names, frees the space unused by the tables and its indexes, and re-imports their statistics.