Note: DBMS_REDEFINITION is used to redefine table columns and column names. Tables that can not be redefined are:

• Tables that have materialized views and materialized view logs defined on them cannot be redefined online
• Tables that are materialized view container tables and AQ tables cannot be redefined online.
• The overflow table of an IOT table cannot be redefined online.

Source

{ORACLE_HOME}/rdbms/admin/dbmshord.sql

Security Model

Execute is granted to execute_catalog_role

The following privileges must be granted to the user:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following privileges may be required too:
CREATE ANY INDEX
CREATE ANY TRIGGER

ABORT_REDEF_TABLE

Cleans Up Errors From The Redefinition Process

dbms_redefinition.abort_redef_table(
uname      IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);

CAN_REDEF_TABLE

Determines if a given table can be redefined online

dbms_redefinition.can_redef_table(
uname        IN VARCHAR2,
tname        IN VARCHAR2,
options_flag IN BINARY_INTEGER := 1,
part_name    IN VARCHAR2 := NULL);

-- see constants above

COPY_TABLE_DEPENDENTS (copy_mvlog added in 11g)

Copies the dependant objects of the original table to the interim table

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

FINISH_REDEF_TABLE

Registers a dependent object (index, trigger or constraint)

dbms_redefinition.finish_redef_table(
uname      IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);

REGISTER_DEPENDENT_OBJECT

Completes The Redefinition Process

dbms_redefinition.register_dependent_object(
uname         IN VARCHAR2,    -- schema name
orig_table    IN VARCHAR2,    -- table to redefine
int_table     IN VARCHAR2,    -- interim table
dep_type      IN PLS_INTEGER, -- type of dependent object
dep_owner     IN VARCHAR2,    -- owner of dependent object
dep_orig_name IN VARCHAR2,    -- name of orig dependent object
dep_int_name IN VARCHAR2);   -- name of interim dependent obj.

START_REDEF_TABLE

Starts The Redefinition Process

dbms_redefinition.start_redef_table(
uname        IN VARCHAR2,            -- schema name
orig_table   IN VARCHAR2,            -- table to redefine
int_table    IN VARCHAR2,            -- interim table
col_mapping IN VARCHAR2 := NULL,    -- column mapping
options_flag IN BINARY_INTEGER := 1, -- redefinition type
orderby_cols IN VARCHAR2 := NULL,    -- col list & ASC/DESC
part_name    IN VARCHAR2 := NULL);

SYNC_INTERIM_TABLE

Maintains Synchronization Between The Original And Interim Table

dbms_redefinition.sync_interim_table(
uname      IN VARCHAR2, -- schema name
orig_table IN VARCHAR2, -- original table
int_table IN VARCHAR2, -- interim table
part_name IN VARCHAR2 := NULL);

UNREGISTER_DEPENDENT_OBJECT

Unregisters a dependent object

dbms_redefinition.unregister_dependent_object(
uname         IN VARCHAR2,
orig_table    IN VARCHAR2,
int_table     IN VARCHAR2,
dep_type      IN PLS_INTEGER,
dep_owner     IN VARCHAR2,
dep_orig_name IN VARCHAR2,
dep_int_name IN VARCHAR2);