Oracle Redefining Tables Online

In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
(1)Improve the performance of queries or DML
(2)Accommodate application changes
(3)Manage storage

Features of Online Table Redefinition

Online table redefinition enables you to:

(1)Modify the storage parameters of a table or cluster
(2)Move a table or cluster to a different tablespace
(3)Add, modify, or drop one or more columns in a table or cluster
(4)Add or drop partitioning support (non-clustered tables only)
(5)Change partition structure
(6)Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
(7)Change physical properties of a materialized view log or an Oracle Streams Advanced Queuing queue table
(8)Add support for parallel queries
(9)Re-create a table or cluster to reduce fragmentation
(10)Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
(11)Convert a relational table into a table with object columns, or do the reverse.
(12)Convert an object table into a relational table or a table with object columns, or do the reverse.
Note:
If it is not important to keep a table available for DML when moving it to another tablespace, you can use the simpler ALTER TABLE MOVE command.
See "Moving a Table to a New Segment or Tablespace".

Online Table Redefinition Examples

Example 1

實驗說明：
HR使用者new_cust表，所在表空間cust_tbs的SEGMENT SPACE手動管理，效能較差，
透過線上重定義，將HR.new_cust表從cust_tbs表空間遷移到USERS表空間；

SQL> create tablespace cust_tbs datafile '/u01/app/oracle/oradata/orcl/cust_tbs01.dbf' size 10m extent management local SEGMENT SPACE MANAGEMENT MANUAL;
SQL> conn /as sysdba
SQL> create table HR.new_cust(x number constraint pk_new_cust primary key) tablespace cust_tbs;
SQL> insert into HR.new_cust select rownum from dual connect by rownum<=5;
SQL> commit;

SQL> select tablespace_name,table_name from dba_tables where table_name like 'NEW_CUST';
TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
CUST_TBS                       NEW_CUST

SQL> select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name like 'CUST_TBS';
TABLESPACE_NAME                SEGMEN
------------------------------ ------
CUST_TBS                       MANUAL

SQL> conn hr/hr
Connected.
SQL> select index_name,table_name from user_indexes where table_name like '%NEW_CUST';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
PK_NEW_CUST                    NEW_CUST

SQL> select constraint_name,table_name from user_constraints where table_name like '%NEW_CUST';
CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
PK_NEW_CUST                    NEW_CUST

1.Verify that the table is a candidate for online redefinition. 
In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
SQL> conn / as sysdba 
SQL>
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','NEW_CUST',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
PL/SQL procedure successfully completed.

2.Create an interim table hr.int_NEW_CUST.
SQL> conn hr/hr   
SQL> create table hr.int_NEW_CUST(x number,constraint int_pk_NEW_CUST primary key (x)) tablespace users;

3.Start the redefinition process.
SQL>
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'NEW_CUST','int_NEW_CUST','x x',dbms_redefinition.cons_use_pk);
END;
/

Note:
The best approach is to define the interim table with a primary key constraint, 
use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, 
and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. 
This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.

SQL>
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'HR',
orig_table => 'NEW_CUST',
int_table => 'INT_NEW_CUST',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'HR',
dep_orig_name => 'pk_NEW_CUST',
dep_int_name => 'Int_pk_NEW_CUST');
END;
/ 
PL/SQL procedure successfully completed.

SQL>
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'HR',
orig_table => 'NEW_CUST',
int_table => 'INT_NEW_CUST',
dep_type => DBMS_REDEFINITION.CONS_CONSTRAINT,
dep_owner => 'HR',
dep_orig_name => 'pk_NEW_CUST',
dep_int_name => 'Int_pk_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.

4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on hr.int_NEW_CUST.)
注：Register 索引和約束操作要在copy 之前，否則需要完成線上重定之後，手工將索引和約束rebuilt 到users表空間
SQL>
DECLARE
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'NEW_CUST','int_NEW_CUST',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
PL/SQL procedure successfully completed.


5.Query the DBA_REDEFINITION_ERRORS view to check for errors.
SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
no rows selected

6.Optionally, synchronize the interim table hr.int_NEW_CUST.
SQL> 
BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.

7.Complete the redefinition.
SQL>
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'NEW_CUST', 'INT_NEW_CUST');
END;
/
PL/SQL procedure successfully completed.

8.
SQL> CONN hr/hr
Connected.
SQL> select table_name,tablespace_name from user_tables where table_name like '%NEW_CUST';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
INT_NEW_CUST                   CUST_TBS
NEW_CUST                       USERS

SQL> set linesize 100
SQL> select index_name,tablespace_name,table_name from user_indexes where table_name like '%NEW_CUST';
INDEX_NAME                     TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------ ------------------------------
INT_PK_NEW_CUST                CUST_TBS                       INT_NEW_CUST
PK_NEW_CUST                     USERS                          NEW_CUST


Example 4

Redefines a single table partition, moving it to a different tablespace
This example demonstrates redefining a single partition. 
It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. 
The table containing the partition to be redefined is defined as follows:

實驗說明：
將SALESTABLE表sal03q1分割槽，由USERS表空間線上遷移到TBS_LOW_FREQ表空間；
SQL> create user steve identified by oracle;  
SQL> grant connect,resource to steve;
SQL> conn steve/oracle
SQL> CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));  

Table created.

SQL> CREATE INDEX sales_index ON salestable (s_saledate, s_productid, s_custid) LOCAL;
Index created.

SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'SALESTABLE';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        USERS
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS

Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).

1.Ensure that salestable is a candidate for redefinition.

SQL> conn / as sysdba
Connected.
SQL>
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'STEVE',
   tname        => 'SALESTABLE',
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
   part_name    => 'sal03q1');
END;
/  

PL/SQL procedure successfully completed.

2.Create the interim table in the TBS_LOW_FREQ tablespace. 
Because this is a redefinition of a range partition, the interim table is nonpartitioned.

SQL> create tablespace TBS_LOW_FREQ datafile '/u01/app/oracle/oradata/orcl/tbs_low_freq01.dbf' size 10M autoextend on maxsize 2G;
SQL> conn STEVE/oracle
SQL> CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;  

Table created.


3.Start the redefinition process using rowid.
SQL>
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => 'STEVE',
   orig_table   => 'salestable',
   int_table    => 'int_salestable',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
   part_name    => 'sal03q1');
END;
/  

PL/SQL procedure successfully completed.

4.Manually create any local indexes on the interim table.
SQL> conn steve/oracle
Connected.
SQL> CREATE INDEX int_sales_index ON int_salestable 
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;  

Index created.

5.Optionally synchronize the interim table.
SQL>
BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
   uname      => 'STEVE', 
   orig_table => 'salestable', 
   int_table  => 'int_salestable',
   part_name  => 'sal03q1');
END;
/  

PL/SQL procedure successfully completed.

6.Complete the redefinition.
SQL>
BEGIN 
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
   uname      => 'STEVE', 
   orig_table => 'salestable', 
   int_table  => 'int_salestable',
   part_name  => 'sal03q1');
END;
/  
PL/SQL procedure successfully completed.

7.Wait for any long-running queries against the interim table to complete, and then drop the interim table.
The following query shows that the oldest partition has been moved to the new tablespace:

SQL> conn steve/oracle
Connected.
SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'SALESTABLE';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1                        TBS_LOW_FREQ
SAL03Q2                        USERS
SAL03Q3                        USERS
SAL03Q4                        USERS


更詳細的內容參見官方文件：

Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration/Database Administrator's Guide/20 Managing Tables/Redefining Tables Online

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！