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