1. 主要步骤及基本参数
主要的重定义步骤包括：
- 检查该表是否可以在线重定义
- 建立在线重定义需要的中间表
- 执行在线重定义
- 检验各分区数据是否正确
2. 检查该表是否可以在线重定义
exec dbms\_redefinition.can\_redef\_table('SCOTT', 'EMP');
PL/SQL procedure successfully completed.
如果没有报错说明该表可以进行重定义，有错误时会给出错误信息，如：
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."EMP" with no primary key
ORA-06512: at "SYS.DBMS\_REDEFINITION", line 242
ORA-06512: at "SYS.DBMS\_REDEFINITION", line 5439
ORA-06512: at line 1
3. 建立在线重定义需要的中间表
3.1 获取原表创建语句
SYS@orcl> set long 9999
SYS@orcl> set pagesize 999
SYS@orcl> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
3.2 创建所需中间表
在确定分区键、分区间隔（INTERVAL；可以不指定此参数，手动分配分区间隔）后，修改原表语句：
SYS@orcl> CREATE TABLE "SCOTT"."EMP_P"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_P_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PARTITION BY RANGE ("HIREDATE") INTERVAL(NUMTOYMINTERVAL(1,'MOUTH'))
(PARTITION VALUES LESS THAN (TO_DATE(' 1980-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS";
Table created.
4. 执行在线重定义
4.1启动在线重定义
SYS@orcl> exec dbms_redefinition.start_redef_table('SCOTT', 'EMP', 'EMP_P');
PL/SQL procedure successfully completed.
4.2创建索引
查看原表中的索引：
SYS@orcl> select INDEX_NAME from dba_indexes where table_name = 'EMP';
INDEX_NAME
------------------------------------------------------------------------------
PK_EMP
TEST_IDX
获取原索引的创建语句：
SYS@orcl> set long 9999
SYS@orcl> set pagesize 999
SYS@orcl> select dbms_metadata.get_ddl('INDEX','TEST_IDX','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','TEST_IDX','SCOTT')
------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."TEST_IDX" ON "SCOTT"."EMP" ("HIREDATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
修改并在中间表上创建索引：
CREATE INDEX "SCOTT"."TEST_IDX_P" ON "SCOTT"."EMP_P" ("HIREDATE") LOCAL
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS";
检查索引是否创建成功：
SYS@orcl> select INDEX_NAME from dba_indexes where table_name = 'EMP_P';
INDEX_NAME
------------------------------------------------------------------------------
PK_P_EMP
TEST_IDX_P
4.3同步
使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改：
SYS@orcl> exec dbms_redefinition.sync_interim_table('SCOTT', 'EMP', 'EMP_P');
PL/SQL procedure successfully completed.
4.4结束在线重定义
SYS@orcl> exec dbms_redefinition.finish_redef_table('SCOTT', 'EMP', 'EMP_P');
PL/SQL procedure successfully completed.
5. 验证数据
检查数据量是否一致：
SYS@orcl> select count(*) from SCOTT.EMP;
COUNT(*)
----------
14
SYS@orcl>select count(*) from SCOTT.EMP_P;
COUNT(*)
----------
14
检查各分区数据是否正确：
SYS@orcl> select table_name, partition_name from dba_tab_partitions where table_name = 'EMP';
SYS@orcl> select * from EMP partition(SYS_P5741);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ------------ --------- ---------- ------------ ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
6. 删除中间表
SYS@orcl> drop table EMP_P purge;
Table dropped.
7. 注意事项
1、在开始dbms_redefinition之前使用dbms_metadata.get_ddl将原表建表语句、索引语句提取出来；
2、确保表中有主键，否则dbms_redefinition.can_redef_table执行不成功；
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."EMP" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 242
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5439
ORA-06512: at line 1
以上报错说明表中缺少主键，要先创建主键后再次执行dbms_redefinition.can_redef_table
3、创建中间表时，修改表名、主键名称、添加PARTITION参数；
4、INTERVAL 参数
INTERVAL后跟数字转换函数（NUMTODSINTERVAL、NUMTOYMINTERVAL），可以将数字转换成相应的日期单位时间，语法：
NUMTOYMINTERVAL ( n , 'char_expr' )
char_expr：日期描述，可以是YEAR和MONTH；
NUMTODSINTERVAL ( n , 'char_expr' )
char_expr：可以是DAY、HOUR、MINUTE、SECOND；
不同的日期单位需要使用对应的函数，否则会发生一下报错：
ERROR at line 19:
ORA-14752: Interval expression is not a constant of the correct type
5、重定义完成后，两张表进行互换，所以此时中间表已经没有意义，但在生产环境下，重定义后的中间表建议保留一周，没有问题后再drop.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
