Oracle在线重定义

会计考友

1 创建测试表
用sys用户创建测试表
( K" I# G5 [/ u' K# M. l: U) r. `SQL> CREATE TABLE HR.ST (ID NUMBER, TIME DATE);
  K1 {% \* E$ q+ E& V' @Table created.
SQL> INSERT INTO HR.ST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
" K- T  a1 Q! H8 A: y4 E50416 rows created.
SQL> commit;
0 T. [  f/ o5 b9 B" S/ sCommit complete.
$ I  g, Z% M/ Z2 授权
SQL> grant execute on DBMS_REDEFINITION to hr;
Grant succeeded.
9 z; L5 O" @( g1 y2 Z9 YSQL> grant CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE to hr;
: e. j! c& Q6 m: o4 ^2 r1 jGrant succeeded.
1 s/ x: e- g4 p- ?6 Y4 N, y( w3 新建中间表(分区的表结构)
/ X. C0 Q; p0 O! X+ VSQL> create table mid_st(id number primary key, time date) partition by range(time)
- \! s" [  g6 @  Z: f& [/ b  2  (
0 N- n+ `# X& S% s! p  3    partition p1 values less than(to_date('2004-7-1', 'yyyy-mm-dd')),
3 o; u# u0 p: d* q3 j5 S+ N: i  4    partition p2 values less than(to_date('2005-1-1', 'yyyy-mm-dd')),
2 m# }3 Y- ]) y8 S' Y, ~  5    partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),
: i/ K, h3 F0 r2 Y* W# y  6    partition p4 values less than(maxvalue)
  7  );
: X9 S$ Q/ Z! ]* F6 w2 m+ yTable created
4 在线重定义
$ d, S2 J9 q- ~9 v+ kSQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');
PL/SQL procedure successfully completed
1 \3 c. _' c# c9 H2 P- V5 同步数据(可选)
如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。
: s4 W) @# }* x- T6 ^3 }( k; y& ~SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'ST', 'MID_ST');
, m- q( ?6 O: }5 iPL/SQL procedure successfully completed
6 结束重定义
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'ST', 'MID_ST');
% S  n, `7 L7 o' [+ g! y* n6 y* BPL/SQL procedure successfully completed
4 x3 `: K- A0 Q. y2 p9 P+ j' a7 查看分区结果
SQL> select a.table_name, a.partition_name from user_tab_partitions a;
TABLE_NAME                     PARTITION_NAME
! c0 t% J( M9 _9 @" z( v6 w0 ]------------------------------ ------------------------------
9 q$ X6 q. Z* X- _# CST                             P1
ST                             P2
ST                             P3
% ?" Z. C- a$ c. l9 O) J% F8 h- C: RST                             P4
8 放弃在线重定义
) j! W9 _( Y) Q7 Z# @2 q, X) @; W可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：
+ V( C9 K7 E0 U( @: d  x( \6 p( cDBMS_REDEFINITION.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。
9 常见问题
  O" ?0 T$ @7 S$ O9.1 无主键
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);
( O1 P1 ^& [4 r" FBEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK); END;
* P  V1 q+ F- ~4 h" C*
ERROR at line 1:
ORA-12089: cannot online redefine table "HR"."ST" with no primary key
8 m) e  ?- ~: L1 Q  x( O& ?  VORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
# @9 P; ?' O8 |$ }, W+ \) s出错了, 该表上缺少主键, 为该表建主键. 再执行验证.
SQL> alter table st add constraint pk_t primary key(id);
( I- e3 i* P! q9 a& PTable altered.中 华 考 试 网
$ X- C0 _- I1 A2 W7 a4 c- `3 y: W用这句删除materialized view 即可继续进行
6 U6 m/ @3 w2 X1 M( Q- Jdrop materialized view log on ;
2 ~3 }' Z+ J- _. W+ bdrop materialized view log on t; OR drop materialized t;
1 T2 e2 d* y0 M9.2 未授权
! U  L2 V# Z4 h* mSQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');
: }3 N7 S0 _$ }5 ~, rbegin DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST'); end;
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
" G( ~) P: @& S: {ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2