線上重定義多用於以下場景
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.

參考連結： ... 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 --

【REDEFINITION】不可使用dbms_redefinition完成列型別的調整（ORA-42016）

相關文章