Oracle Database - (Online table) Redefinition (DBMS_REDEFINITION)

About

Online table redefinition is a mechanism to make table structure modifications without significantly affecting the availability of the table.

You can perform online table redefinition:

with the Oracle Enterprise Manager Cloud Control (Cloud Control) Reorganize Objects wizard
or with the apps/search/search.jsp package.

Articles Related

Privileges

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

grant EXECUTE_CATALOG_ROLE to sh;
grant execute on DBMS_REDEFINITION to sh;

In addition to having execute privileges on this package, you must be granted the following privileges:

CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

CREATE ANY TRIGGER
CREATE ANY INDEX

Features

table's storage properties: REDEF_TABLE procedure
Move a table or cluster to a different tablespace (Can also be done with ALTER TABLE MOVE)
…

Steps

CAN_REDEF_TABLE

CAN_REDEF_TABLE determines if a given table can be redefined online. This is the first step of the online redefinition process. If the table is not a candidate for online redefinition, an error message is raised.

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'Schema name of the table',
    tname        => 'Name of the table to be re-organized',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
    part_name    => 'Name of the partition being redefined');
END;
/

where:

uname is the Schema name of the table (of just USER in SQLPlus)
tname is the name of the table to be re-organized
options_flag is:
- dbms_redefinition.cons_use_pk: The redefinition is done using:
  - primary keys (default)
  - or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints).
- dbms_redefinition.cons_use_rowid: the redefinition is done using:
  - rowids.

Create an interim table

I have just added partitions

CREATE
  TABLE "COSTS_INTERIM"
  (
    "PROD_ID"    NUMBER NOT NULL ENABLE,
    "TIME_ID"    DATE NOT NULL ENABLE,
    "PROMO_ID"   NUMBER NOT NULL ENABLE,
    "CHANNEL_ID" NUMBER NOT NULL ENABLE,
    "UNIT_COST"  NUMBER( 10, 2 ) NOT NULL ENABLE,
    "UNIT_PRICE" NUMBER( 10, 2 ) NOT NULL ENABLE
  ) 
  PARTITION BY HASH(TIME_ID) PARTITIONS 5;

Start the redefinition process

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
     uname        => USER,
     orig_table   => costs,
     int_table    => costs_interim,
     options_flag => dbms_redefinition.cons_use_rowid);
END;
/

Example

Online Table Redefinition Examples

Support

table or view does not exist

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 170
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2795
ORA-06512: at line 2

The table given as parameter doesn't exist.