Oracle数据迁移—一张7.5亿行的表（没有做分区，没有优化索引）进行优化测试，并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法4—在线重定义
一、在线重定义的概念及功能
转自：https://www.cnblogs.com/hfliyi/p/3626302.html
1、概念
在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。
Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。
而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。
2、在线重定义表具有以下功能：
修改表的存储参数； 可以将表转移到其他表空间； 增加并行查询选项； 增加或删除分区； 重建表以减少碎片； 将堆表改为索引组织表或相反的操作； 增加或删除一个列； 调用DBMS_REDEFINITION包需要 EXECUTE_CATALOG_ROLE 角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
3、在线重定义表的步骤如下：
（1）选择一种重定义方法：
存在两种重定义方法，一种是基于主键，另一种是基于ROWID。
ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。
（2）验证表是否满足在线重定义
调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程，如果表不满足重定义的条件，将会报错并给出原因。
（3）创建空的中间表
在用一个方案中建立一个空的中间表，根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等。
（4）开始在线重定义
调用DBMS_REDEFINITION.START_REDEF_TABLE()过程，并提供下列参数：被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。
如果映射方法没有提供，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列。如果没有给出重定义方法，则认为使用主键方式。
（5）中间简历触发器/索引/约束
在中间表上建立触发器、索引和约束，并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。
当重定义完成时，中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。
（6）可选-增量过程
如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，
那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。
（7）结束在线重定义
执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关。
执行完FINISH_REDEF_TABLE()过程后，原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。
（8）可选-可以重命名索引、触发器和约束。
对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$); ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
4、下面是进行重定义操作后的结果：
原始表根据中间表的属性和特性进行重定义；
START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权，现在定义在原始表上。
中间表上disabled的约束在原始表上处于enabled状态。
原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。
任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。
如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
其中UNAME 参数是指用户；
二、在线重定义案例
1、选择基于主键的在线重定义（默认）
2、检测当前表SFC_BROOKNER_PARAM_LOG是否满足在线重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', DBMS_REDEFINITION.CONS_USE_PK);
3、创建中间表（分区表）
（1）给所有分区预创建表空间
create tablespace TBSP_SFCBROOKPARAMLOG_2020 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2020 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2020 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_03.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2021 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2022 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2023 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2024 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2024 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024_02.dbf' size 1G autoextend on;
（2）创建表，并进行范围分区
注：此处有主键，会创建一个主键索引，此索引是普通索引（全局非分区索引）。
create table mesprd.sfc_brookner_param_log_p
(
id varchar2(32) default sys_guid() not null enable,
datetime_created date default sysdate not null enable,
user_created varchar2(80) default 'sys' not null enable,
datetime_modified date,
user_modified varchar2(80),
state char(1) default 'a' not null enable,
enterprise_id varchar2(32) default '*' not null enable,
org_id varchar2(32) not null enable,
workcenter_code varchar2(80) not null enable,
equipment_code varchar2(80) not null enable,
param_code varchar2(80) not null enable,
param_name varchar2(240) not null enable,
param_point varchar2(240) not null enable,
actual_value varchar2(2000),
incident_desc varchar2(2000),
bruckner_code varchar2(80) not null enable,
is_recom char(1) default 'n' not null enable,
primary key (id)
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 mesprd_tbsp 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)
partition by range(datetime_created)(
partition p_2020 values less than(to_date('2021/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2020,
partition p_2021 values less than(to_date('2022/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2021,
partition p_2022 values less than(to_date('2023/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2022,
partition p_2023 values less than(to_date('2024/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2023,
partition p_2024 values less than(to_date('2025/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2024
);
4、开始在线重定义
（1）开始重定义
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P', 'DBMS_REDEFINITION.CONS_USE_PK');
或可以改为默认（使用主键在线重定义）：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
（2）提示完成重定义
（3）查看数据量
5、如果中间原始表有数据更新，需要执行异步传输（此时需要前端业务停止执行，并将此表设置为只读模式，以免原表有新数据写入）
（1）模拟数据删除（一条）
delete from SFC_BROOKNER_PARAM_LOG where rownum<2;
（2）设置表为只读权限（加锁）
ALTER TABLE SFC_BROOKNER_PARAM_LOG READ ONLY;
--如果后期要解锁，可以设置表的读写权限（解锁）
ALTER TABLE SFC_BROOKNER_PARAM_LOG READ WRITE;
（3）更新新增数据到中间表
SQL> EXEC dbms_redefinition.sync_interim_table('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
现在，将中间表与原始表同步，仅当要对表SFC_BROOKNER_PARAM_LOG进行更新时才需要执行该操作。
（4）执行完成，查看数据是否少一行。
SQL>select count(*) from mesprd.SFC_BROOKNER_PARAM_LOG_P;
6、手动结束在线重定义任务
SDL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
7、失败的情况解
如果重组织失败（可能是因为temp空间不足等，如下；需要先解决失败情况后再重新开始。），那么你就必须采取特殊的步骤来让它重新开始。由于重定义过程需要创建表格的快照，因此为了重新开始这一过程，你必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。
DBMS_REDEFINITION.ABORT_REDEF_TABLE过程有三个参数，即用户（schema）、原始表格（original table name）名称以及持有表格名称（holding table name）。
它“出栈”并允许你开始重组织表格。
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
8、数据查看测试（和原表进行对比）
SELECT COUNT(*) FROM SFC_BROOKNER_PARAM_LOG_P;
SELECT COUNT(*) FROM FC_BROOKNER_PARAM_LOG_P PARTITION (p_2020); SELECT COUNT(*) FROM T PARTITION (p_2021);
需要说明的是完成后，原表和中间表的结构也同时进行了交换，并且中间表里面有原表的数据备份。
9、收集新表的统计信息—耗时：163秒
begin dbms_stats.gather_table_stats(ownname => 'MESPRD', tabname => 'SFC_BROOKNER_PARAM_LOG_P'); end;
10、创建本地分区索引或全局分区索引
注意：主键列已经自行创建了索引。如下：
根据需要的列，创建分区索引。
（1）创建索引表空间
create tablespace ind_SFC_BROOKNER_PARAM_LOG_P datafile '/oracle/oradata2/ind_SFC_BROOKNER_PARAM_LOG_01.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
（2）时间列-datectime_reated的索引必须要创建（创建本地分区索引）
如果要创建全局索引，麻烦一些，分区删除时，需要重新更新索引。使用本地分区索引则不会有此问题。不过全局也可解决，在删除分区时进行update也可以。
--create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG_P(datetime_created) global tablespace ind_SFC_BROOKNER_PARAM_LOG; --153秒 create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG_P(datetime_created) local tablespace ind_SFC_BROOKNER_PARAM_LOG;
（2）其他组合索引（开发沟通索引重建问题，需提前沟通）
11、删除原表
drop table SFC_BROOKNER_PARAM_LOG purge;
12、重命名新表
rename SFC_BROOKNER_PARAM_LOG to SFC_BROOKNER_PARAM_LOG;
13、生产测试
三、在线重定义的优缺点
1、优点：
--保证数据的一致性，在大部分时间内，表原表都可以正常进行DML操作。
--只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。
--而且可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
2、缺点：
--你必须有足以维护两份表格拷贝的空间。 --你不能更改主键栏。 --表格必须有主键。 --必须在同一个大纲中进行表格重定义。 --在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。 --表格不能包含LONG、BFILE以及用户类型（UDT）。 --不能重定义链表（clustered tables）。 --不能在SYS和SYSTEM大纲中重定义表格。 --不能用具体化视图日志（materialized view logs）来重定义表格；不能重定义含有具体化视图的表格。 --不能在重定义过程中进行横向分集（horizontal subsetting）。
