Online table redefinition allows to restructure table with little affect to table availability.  The table is open for queries and DML for most of the redefine procedure, but is locked in exclusive mode for a short time in process.  Redefinition is available in EM Cloud and through DBMS_REDEFINITION package.

Three enhancements in 12c for redefine:

1. Multiple Partitions – you can create multiple partitions in single redefinition execution. This reduces time for re-partitioning but requires more space, because an interim table is built for each partition creation.
2. Tables with VPD Policies – You can now redefine tables with Virtual Private Database policies. The copy_vpd_opt parameter of START_REDEF_TABLE procedure must set to handle VPD policies during process.   Parameter can be set to:
   1. CONS_VPD_NONE – This is the default, for use when VPD not in user, error will occur on a VPD table.
   2. CONS_VPD_AUTO – VPD policies will be copied to new table during redefinition process.
   3. CONS_VPD_MANUAL – VPD policies must be manually moved to new table, done when structural changes are made that affect VPD policies.
3. Lock Timeout for FINISH_REDEF_TABLE – You can specify a number of seconds that a redefine process will hold an exclusive lock for swapping interim table. If it is exceeded redefine will exit.
4. REDEF_TABLE – New procedure in the DBMS_REDEFINITION package, which allows for table redefinition in a single step to change the following:
   1. Change the tablespace for a table, partition, index or LOB storage.
   2. Compression changes including table, partition, index or LOBs
   3. LOB column changes for SECUREFILE or BASICFILE storage.

Larry Catt

OCP