1）修改表的存储参数
2）调整表空间
3）增加或删除分区
4）将堆表改变为索引组织表
5）增加加或删除列
其中堆表调整为分区表的应用场景较普遍。
参考一下Oracle官方文档中关于在线重定义的描述。
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
Online table redefinition enables you to:
- Modify the storage parameters of a table or cluster
- Move a table or cluster to a different tablespace in the same schema
- Add, modify, or drop one or more columns in a table or cluster
- Add or drop partitioning support (non-clustered tables only)
- Change partition structure
- Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
- Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
- Add support for parallel queries
- Re-create a table or cluster to reduce fragmentation
- Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
- Convert a relational table into a table with object columns, or do the reverse.
- Convert an object table into a relational table or a table with object columns, or do the reverse.
参考链接：http://download.oracle.com/docs/ ... bles.htm#ADMIN01514
注意因为在线重定义过程中要求列的属性要相同，因此不可使用dbms_redefinition完成列类型的调整，实验验证之。
1.创建表T1，包含一个NUMBER类型的列
sec@ora10g> create table T1 (x NUMBER(19) primary key);
sec@ora10g> insert into t1 select rownum from all_objects;
11946 rows created.
sec@ora10g> commit;
Commit complete.
2.创建中间表T1
sec@ora10g> create table T2 (x varchar2(20) primary key);
3.授予用户所需要的权限
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> grant EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE to sec;
Grant succeeded.
4.尝试使用dbms_redefinition在线重定义
sec@ora10g> exec dbms_redefinition.abort_redef_table('SEC','T1','T2');
PL/SQL procedure successfully completed.
sec@ora10g> exec dbms_redefinition.can_redef_table('SEC','T1');
PL/SQL procedure successfully completed.
sec@ora10g> exec dbms_redefinition.start_redef_table('SEC','T1','T2');
BEGIN dbms_redefinition.start_redef_table('SEC','T1','T2'); END;
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
5.关于ORA-42016错误的解释
sec@ora10g> !oerr ora 42016
42016, 0000, "shape of interim table does not match specified column mapping"
// *Cause: The number of columns, or the type or the length semantics of a
// column, in the interim table did not match the specified
// column mapping.
// *Action: Ensure that the interim table matches the column mapping by
// either modifying the column mapping string or altering the
// interim table's column definition(s).
6.小结
在使用一项新技术之前要充分了解本身的优势和劣势，这样会提高我们工作效率。
Good luck.
secooler
10.03.18
-- The End --