Claiming space from table using DBMS_REDEFINITION

posted Jun 16, 2011, 8:46 AM by Sachchida Ojha   [ updated Jun 29, 2011, 10:44 AM ]
The DBMS_REDEFINITION package provides an interface to perform an online redefinition of tables. With DBMS_REDEFINITION, you can perform an online reorganization of tables. To achieve this online reorganization, incrementally maintainable local materialized views are used. Snapshot logs need to be defined on the master tables to support incrementally maintainable materialized views. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization. To keep table indexes and privileges you must use the copy_table_dependents procedure.

Following privileges are needed to run this package: 
  - Execute privilege to DBMS_REDEFINITION
  - Create any table
  - Alter any table
  - Drop any table
  - Lock any table
  - Select any table

Summary of DBMS_REDEFINITION Subprograms:
  =========================================
 
  CAN_REDEF_TABLE Procedure:
  --------------------------
  This procedure determines if a given table can be reorganized online. This is the first step of the online reorganization process. If the table is not a candidate for online redefinition, an error message is raised.
 
  SYNTAX
 
  DBMS_REDEFINITION.can_redef_table (
  uname IN VARCHAR2,
  tname IN VARCHAR2);
 
  CAN_REDEF_TABLE Procedure Parameters:
 
  Parameter          Description
  ---------          ------------
  uname              The schema name of the table.
  tname              The name of the table to be reorganized.
 
 
 
  START_REDEF_TABLE Procedure:
  ----------------------------
  This procedure initiates the reorganization process. After verifying that the   table can be reorganized online, you create an empty interim table (in the same  schema as the table to be reorganized) with the desired attributes of the
  post-reorganization table.
 
  SYNTAX
 
  DBMS_REDEFINITION.start_redef_table (
  uname IN VARCHAR2,
  orig_table IN VARCHAR2,
  int_table IN VARCHAR2,
  col_mapping IN VARCHAR2 := NULL);
 
  START_REDEF_TABLE Procedure Parameters:
 
  Parameter          Description
  ----------         ------------
  uname                      The schema name of the tables.
  orig_table               The name of the table to be reorganized.
  int_table                  The name of the interim table.
  col_mapping         The mapping information from the columns in the interim table to the columns in the original table. (This is similar to the column list on the SELECT clause of a query.) If NULL, all the columns in the original table are                                                 selected and have the same name after reorganization.
 
  FINISH_REDEF_TABLE Procedure:
  ----------------------------
  This procedure completes the reorganization process.  Before this step you can  create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled.  After
  completing this step, the original table is locked briefly during this procedure.
 
  
  SYNTAX
 
  DBMS_REDEFINITION.finish_redef_table (
  uname IN VARCHAR2,
  orig_table IN VARCHAR2,
  int_table IN VARCHAR2);
 
  FINISH_REDEF_TABLE Procedure Parameters:
 
  Parameter           Description
  ---------           ------------
  uname               The schema name of the tables.
  orig_table          The name of the table to be reorganized.
  int_table           The name of the interim table.
 
 
 
  SYNC_INTERIM_TABLE Procedure:
  ----------------------------
  This procedure keeps the interim table synchronized with the original table.   This step is useful in minimizing the amount of synchronization needed to be done by finish_reorg_table before completing the online reorganization. This
  procedure can be called between long running operations (such as create index) on the interim table to sync it up with the data in the original table and  speed up subsequent operations.
 
  SYNTAX
 
  DBMS_REDEFINITION.sync_interim_table (
  uname IN VARCHAR2,
  orig_table IN VARCHAR2,
  int_table IN VARCHAR2);
 
  SYNC_INTERIM_TABLE Procedure Parameters:
 
  Parameters          Description
  ----------          ------------
  uname               The schema name of the tables.
  orig_table          The name of the table to be reorganized.
  int_table           The name of the interim table.
 
 
  ABORT_REDEF_TABLE Procedure:
  ----------------------------
  This procedure cleans up errors that occur during the reorganization process.   This procedure can also be used to abort the reorganization process any time  after start_reorg_table has been called and before finish_reorg_table is called.
 
  SYNTAX
 
  DBMS_REDEFINITION.abort_redef_table (
  uname IN VARCHAR2,
  orig_table IN VARCHAR2,
  int_table IN VARCHAR2);
 
  ABORT_REDEF_TABLE Procedure Parameters:
 
  Parameters          Description
  ----------          ------------
  uname               The schema name of the table.
  orig_table          The name of the table to be reorganized.
  int_table           The name of the interim table.
 
 
  If we have an encrypted column which is part of primary key then  follow the steps mentioned in the bug below.
 
  Reference: Bug 6034260 DBMS_REDEFINITION.START_REDEF_TABLE FAILS ORA-32412 BECAUSE
  OF ENCRYPTED COLUMN
 
  COPY_TABLE_DEPENDENTS  (Procedure)
  Copies the dependent objects of the original table to the interim table
 
 
  COPY_TABLE_DEPENDENTS.dbms_redefinition.copy_table_dependents(
  uname               IN  VARCHAR2,
  orig_table           IN  VARCHAR2,
  int_table             IN  VARCHAR2,
  copy_indexes     IN  PLS_INTEGER := 1,
  copy_triggers      IN  BOOLEAN := TRUE,
  copy_constraints IN  BOOLEAN := TRUE,
  copy_privileges   IN  BOOLEAN := TRUE,
  ignore_errors      IN  BOOLEAN := FALSE,
  num_errors       OUT PLS_INTEGER,
  copy_statistics  IN  BOOLEAN := FALSE
copy_mvlog       IN  BOOLEAN := FALSE);
Comments