Oracle数据迁移5—一张7.5亿行的表（没有做分区，没有优化索引）进行优化测试，并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法3—交换分区方式（注：此方式不适合当前方式-此思路可以适用于分区表->独立表的交换。）

2023年6月10日 172点热度 0人点赞 0条评论

一、迁移方法3—交换分区方式

Oracle数据迁移—一张7.5亿行的表（没有做分区，没有优化索引）进行优化测试，并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法3—交换分区方式（注：此方式不适合当前方式）

（注：此方式不适合当前方式-此思路可以适用于分区表->独立表的交换。）

一、迁移方法3—交换分区方式

一个几亿条数据的分区表，要把一些老数据分出来，放一个新建的表上。按时间划分，指定时间以前的放在老数据表，指定时间以后的数据保留在原表，然后把备份老数据表空间truncate掉。按照分区操作分区交换是最好的解决办法。

分区交换技术可以实现数据快速转移，所以在数据加载提速，历史数据清理等方面特别有用。分区交换技术实际上只修改了数据字典中的数据物理段位置，而不是实际的移动数据，所以速度很快。

1、交换原理

可以将一个有数据的分区和一张空的非分区表进行交换，
也可以将一个有数据的表和一个空的分区表进行交换，
也可以将一个有数据的分区表和一个有数据的普通表进行交换

同一个命令，可以来回切换。

2、交换分区需要满足的条件

在Oracle数据库中，交换分区是一项常用的技术。通过交换分区技术，可以将已经存在的分区与新建的分区进行交换。这样的做法可以使得数据库的维护变得更加容易和高效。但是，要进行分区交换也是需要满足一定的条件的。下面是进行分区交换需要满足的条件：

（1）分区必须具有相同的结构

分区交换需要保证交换的分区具有相同的结构。这意味着分区表必须有相同的列名、数据类型和长度。否则，在交换分区时可能会发生数据类型不匹配的错误。

（2）分区必须在同一个表空间中

分区交换需要保证交换的分区在同一个表空间中。如果在不同的表空间中进行交换，可能会有一些问题发生，例如交换失败，数据丢失等。

（3）分区必须相邻

分区交换需要保证交换的分区是相邻的。相邻的分区意味着交换分区的前一个分区和后一个分区中间没有其他的分区。否则，在交换分区时可能会引起其它分区的变化或丢失数据。

（4）分区必须没有参考约束

分区交换需要保证交换的分区没有参考约束，否则交换分区的过程中会失败。如果分区表中有参考约束，可以通过以下代码来关闭参考约束。

ALTER TABLE table_name DISABLE CONSTRNT constrnt_name;

（5）分区必须没有本地索引

分区交换需要保证交换的分区没有本地索引，否则交换分区的过程中会失败。如果分区表中有本地索引，可以通过以下代码来临时禁用索引。

ALTER INDEX index_name UNUSABLE;

--为什么我在12c中测试时，为分区表创建的主键索引就是本地分区索引就可以实现交换？

create table test_p(id number,name varchar2(10),age int)
partition by range(id)(
  partition p_10 values less than(10),
  partition p_20 values less than(20),
  partition p_30 values less than(30),
  partition p_40 values less than(40),
  partition p_50 values less than(50),
  partition p_max values less than(maxvalue)
);

create index idx_test_p on test_p(id) local;
alter table test_p add constraint pk_test_p_id primary key (id);

（6）分区必须没有全局索引

分区交换需要保证交换的分区没有全局索引，否则交换分区的过程中会失败。如果分区表中有全局索引，可以通过以下代码来临时禁用索引。

ALTER INDEX index_name UNUSABLE GLOBAL;

（7）分区必须没有关联分区键

分区交换需要保证交换的分区没有关联分区键，否则交换分区的过程中会失败。如果分区表中有关联分区键，可以通过以下代码来删除此键。

ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;

（8）分区必须有足够的表空间

分区交换需要保证交换的分区有足够的空间，否则交换分区的过程中会失败。如果分区表没有足够的空间，可以通过增加表空间或者删除不需要的数据来获得更多的空间。

总结：

通过以上的介绍，我们了解了进行分区交换需要满足的条件。如果我们能够做到按照这些条件来进行分区交换，那么我们就可以在维护数据库时更加容易和高效。

另外，我们需要注意，在进行分区交换之前需要将分区表的数据备份，以防止数据丢失。在备份之后，我们可以使用以下代码来进行分区交换：

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE new_table;

代码中，table_name是原始分区表的名称，partition_name是要交换的分区名称，new_table是我们想要交换的分区表的名称。

2、优点和缺点

（1）优点

可以快速实现分区交换。

（2）缺点：

交换后，原数据表为空，无法在线查询。

3、交换分区满足条件

（1）给分区表创建主键和本地分区索引的方式

因为交换分区需要保证分区表的索引为local，所以在给分区表创建主键的时候要分步骤进行（因为默认创建主键，会自动创建global索引。

create table test_p(id number,name varchar2(10),age int)
  partition by range(id)(
  partition p_10 values less than(10),
  partition p_20 values less than(20),
  partition p_30 values less than(30),
  partition p_40 values less than(40),
  partition p_50 values less than(50),
  partition p_max values less than(maxvalue)
);
--select * from user_indexes where index_name='IDX_TEST_P';
--select * from user_part_indexes;
--select * from user_ind_partitions;
create index idx_test_p on test_p(id) local;
alter table test_p add constraint pk_test_p_id primary key (id);

（2）如何创建中转表

因为创建方式的不同，所以给中专表test创建主键时也要分步进行

create table test(id number,name varchar2(10),age int);
create index idx_test on test(id);
alter table test add constraint pk_test_p primary key (id);

4、操作流程

（1）为每一个表分区单独创建表空间

（2）创建和原表相同的表结构（但需要分区）

（3）创建和原索引相同的分区索引列（但是分区索引需要为local或者在交换分区时不使用including index，后面单独rebuild）

如果为global分区索引，在进行交换时会报错：ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

create table test_p(id number,name varchar2(10),age int)
partition by range(id)(
partition p_10 values less than(10),
partition p_20 values less than(20),
partition p_30 values less than(30),
partition p_40 values less than(40),
partition p_50 values less than(50),
partition p_max values less than(maxvalue)
);
create index idx_test_p on test_p(id) local;
alter table test_p add constraint pk_test_p_id primary key (id);

5、创建交换表分区

（SFC_BROOKNER_PARAM_LOG 创建表空间（用于存放分区表）

（1）给所有分区创建表空间

create tablespace TBSP_SFCBROOKPARAMLOG_2020 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
create tablespace TBSP_SFCBROOKPARAMLOG_2021 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
create tablespace TBSP_SFCBROOKPARAMLOG_2022 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
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;
create tablespace TBSP_SFCBROOKPARAMLOG_2024 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;

（2）创建分区表

注：此处有主键，会创建一个主键索引，此索引是普通索引（全局非分区索引）。因为交换分区和普通表进行分区交换时，分区必须有一个本地索引，或者不导入索引，后期单独创建索引或重建分区表索引。

create table mesprd.sfc_brookner_param_log
( 
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
);

6、创建和原索引相同的分区索引列（但是分区索引需要为local）

（1）创建索引表空间

create tablespace ind_SFC_BROOKNER_PARAM_LOG 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(datetime_created) global tablespace ind_SFC_BROOKNER_PARAM_LOG; --153秒
create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG(datetime_created) local tablespace ind_SFC_BROOKNER_PARAM_LOG;

（3）其他组合索引（开发沟通索引重建问题，需提前沟通）

7、根据筛选条件进行交换分区

（1）分区语句案例

如：将test_p中分区为p_10分区的数和test中的数据进行交换。

alter table test_p
exchange partition p_10
with table test
including indexes without validation;

注:

including indexes： 是将索引（本地索引）也进行交换，如果为全局索引，则会报错；因为创建表时，创建主键会自动创建一个globa索引（解决方法：分区表主键索引，后面单独增加（先给分区表创建本地索引，再创建主键约束即可解决）。
without validation：不进行一系列有效性检查（默认时进行检查）
                    如果默认进行检查，如果交换分区p_10交换到独立表t中，表t和p_20再交换，那么会报错，因为p_10的数据不可能会到p_20中，不符合范围分区约束；
                    如果显式指定without validation，那么p_10的数据可以通过表t，交换到p_20中。

8、方案问题

原表是大表（独立表)，无分区表，新表是分区表。我们需要做的是将原表大表中的数据，根据时间（范围分区）进行筛选，并写入到分区表中。

但是alter table test_p exchange partition p_10 with table test including indexes without validation; 语句并不支持原独立表的条件筛选，于是无法将筛选数据与分区表进行交换。

9、此方式可以解决的问题

此思路可以适用于分区表->独立表的交换。

本作品采用知识共享署名 4.0 国际许可协议进行许可