Oracle Database - (Online table) Redefinition (DBMS_REDEFINITION)

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - 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 DBMS_REDEFINITION package.

3 - 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
Advertising

4 - 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)

5 - Steps

5.1 - 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.
Advertising

5.2 - 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;

5.3 - 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;
/

6 - Example

7 - Support

7.1 - 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.

8 - Documentation / Reference

Advertising
lang/plsql/dbms_redefinition.txt · Last modified: 2017/09/06 22:18 by gerardnico