sec@ora10g> desc t2; Name Null? Type --------------- -------- ------------------ X NOT NULL VARCHAR2(20)
sec@ora10g> select count(*) from t2;
COUNT(*) ---------- 0
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;
关于start_redef_table参数内容的表述请参考下面内容。 PROCEDURE START_REDEF_TABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- UNAME VARCHAR2 IN ORIG_TABLE VARCHAR2 IN INT_TABLE VARCHAR2 IN COL_MAPPING VARCHAR2 IN DEFAULT OPTIONS_FLAG BINARY_INTEGER IN DEFAULT ORDERBY_COLS VARCHAR2 IN DEFAULT PART_NAME VARCHAR2 IN DEFAULT
4）看一下此时目标表T1和中间表T2的结构和数据 sec@ora10g> desc t1; Name Null? Type ------------------- -------- ---------------------- X NOT NULL NUMBER(19)
sec@ora10g> desc t2 Name Null? Type ------------------- -------- ---------------------- X NOT NULL VARCHAR2(20)
sec@ora10g> select count(*) from t1;
COUNT(*) ---------- 11944
sec@ora10g> select count(*) from t2;
COUNT(*) ---------- 11944
此时结构没有变化，数据已经同步到中间表T2表中。
5）模拟目标表T1的事务（以删除为例） sec@ora10g> delete from t1 where rownum<10000;
5.小结 在线重定义功能在保证系统高可用的前提下完成数据库调整带来了非常大的便利。 此文中描述的使用在线重定义修改主键类型的例子并不普遍，在线重定义功能主要还是集中在以下几个场景： 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.
