Oracle在线重定义

会计考友

1 创建测试表
3 ^6 ~8 y3 \$ @2 h用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.
: F6 Z4 K. Z' I- n' \$ X3 BSQL> commit;
6 E- h% G( ]# k; TCommit complete.
2 授权
8 k! k/ B8 n! w* C, C% a$ rSQL> 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;
; b2 d3 }1 g: d, p: vGrant succeeded.
3 新建中间表(分区的表结构)
/ H# ?0 ~; Y# Y, I$ A7 a, lSQL> create table mid_st(id number primary key, time date) partition by range(time)
  2  (
+ }2 ?" m& N- x5 x) e' d  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')),
, f0 d/ K1 U& z& Z. s9 w; V  5    partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),
& `! ]3 c/ B& Q4 w  6    partition p4 values less than(maxvalue)
  7  );
6 s4 M  j( j. z8 `Table created
4 在线重定义
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');
- Q9 \3 `5 F$ z: VPL/SQL procedure successfully completed
+ L7 `, B6 _! L4 R5 同步数据(可选)
, h( \7 T, b/ {$ @/ L  j8 L如果在执行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
% f% b9 U; V1 h; E6 b6 结束重定义
% c8 S) A0 N" k8 z# _SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'ST', 'MID_ST');
PL/SQL procedure successfully completed
7 查看分区结果
9 R& V9 c. ]& h& N% ~# L2 B; eSQL> select a.table_name, a.partition_name from user_tab_partitions a;
TABLE_NAME                     PARTITION_NAME
1 b" w2 U3 F' |2 B. S' q------------------------------ ------------------------------
* w) N6 L3 K1 R9 j; v& r0 d$ r7 G4 JST                             P1
6 X( X: m3 a6 n! }- p/ ?ST                             P2
" M! }2 Q1 x" ?+ BST                             P3
ST                             P4
# |% g7 a7 D1 o9 t' C) \5 Y8 放弃在线重定义
; w9 r# m3 Q0 q3 Z% q可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：
( v$ \) ~! Y' k" B2 dDBMS_REDEFINITION.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。
9 常见问题
9.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;
3 L/ k+ L; A8 O: G& T' x*
. z3 ^: T1 O8 zERROR at line 1:
3 s9 [* B* i! k$ p  yORA-12089: cannot online redefine table "HR"."ST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
# _% K) w, |9 L9 |1 z7 D- [ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
' |2 Q' O4 e& {' pORA-06512: at line 1
9 I1 G4 I& T( v" h  ~& i) T* W5 Z$ _$ Q出错了, 该表上缺少主键, 为该表建主键. 再执行验证.
SQL> alter table st add constraint pk_t primary key(id);
Table altered.中 华 考 试 网
用这句删除materialized view 即可继续进行
drop materialized view log on ;
) J* o, k. H9 A7 c7 jdrop materialized view log on t; OR drop materialized t;
9 z" X3 ?( A' q: h0 g  m$ ~# \0 f, P9.2 未授权
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');
" ^! h/ H8 I7 g  r& k" ]: mbegin DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST'); end;
0 S4 x1 L* L' u0 Q* J5 OORA-01031: insufficient privileges
) u; n: S5 D7 s: U+ D& SORA-06512: at "SYS.DBMS_REDEFINITION", line 50
7 B8 X  p  v0 wORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2