1 创建测试表 用sys用户创建测试表 sqlgt; create table hr.st (id number, time date); table created. sqlgt; insert into
1 创建测试表
用sys用户创建测试表
sql> create table hr.st (id number, time date);
table created.
sql> insert into hr.st select rownum, created from dba_objects;
50416 rows created.
sql> commit;
commit complete.
2 授权
sql> grant execute on dbms_redefinition to hr;
grant succeeded.
sql> grant create any table, alter any table, drop any table, lock any table, select any table to hr;
grant succeeded.
3 新建中间表(分区的表结构)
sql> create table mid_st(id number primary key, time date) partition by range(time)
  2  (
  3    partition p1 values less than(to_date('2004-7-1', 'yyyy-mm-dd')),
  4    partition p2 values less than(to_date('2005-1-1', 'yyyy-mm-dd')),
  5    partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),
  6    partition p4 values less than(maxvalue)
  7  );
table created
4 验证
sql> exec dbms_redefinition.can_redef_table(user, 'st', dbms_redefinition.cons_use_pk);
pl/sql procedure successfully completed
5 在线重定义
sql> exec dbms_redefinition.start_redef_table(user, 'st', 'mid_st');
pl/sql procedure successfully completed
6 同步数据(可选)
如果在执行dbms_redefinition.start_redef_table()过程和执行dbms_redefinition.finish_redef_table()过程直接在重定义表上执行了大量的dml操作，那么可以选择执行一次或多次的sync_interim_table()过程，，以减少最后一步执行finish_redef_table()过程时的锁定时间。
sql> exec dbms_redefinition.sync_interim_table(user, 'st', 'mid_st');
pl/sql procedure successfully completed
7 结束重定义
sql> exec dbms_redefinition.finish_redef_table(user, 'st', 'mid_st');
pl/sql procedure successfully completed
8 查看分区结果
sql> select a.table_name, a.partition_name from user_tab_partitions a;
table_name                     partition_name
------------------------------ ------------------------------
st                             p1
st                             p2
st                             p3
st                             p4
9 放弃在线重定义
可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：
dbms_redefinition.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。
10 常见问题
10.1 无主键
sql> exec dbms_redefinition.can_redef_table(user, 'st', dbms_redefinition.cons_use_pk);
begin dbms_redefinition.can_redef_table(user, 'st', dbms_redefinition.cons_use_pk); end;
*
error at line 1:
ora-12089: cannot online redefine table hr.st with no primary key
ora-06512: at sys.dbms_redefinition, line 137
ora-06512: at sys.dbms_redefinition, line 1478
ora-06512: at line 1
出错了, 该表上缺少主键, 为该表建主键. 再执行验证.
sql> alter table st add constraint pk_t primary key(id);
table altered.
用这句删除materialized view 即可继续进行
drop materialized view log on ;
drop materialized view log on t; or drop materialized t;
10.2 未授权
sql> exec dbms_redefinition.start_redef_table(user, 'st', 'mid_st');
begin dbms_redefinition.start_redef_table(user, 'st', 'mid_st'); end;
ora-01031: insufficient privileges
ora-06512: at sys.dbms_redefinition, line 50
ora-06512: at sys.dbms_redefinition, line 1343
ora-06512: at line 2