目录
迁移方案7-Oracle数据迁移3—一张7.5亿行的表（没有做分区，没有优化索引）进行优化测试，并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法1—expdp/impdp在线迁移
一、迁移指导思想
1、迁移目的
--减少数据占用空间 --便于后期数据清理
2、对比思路（上一节已做对比）
3种方式对比，确保分区后，不能降低查询和插入性能
--分区前的性能 --分区后，使用本地索引的性能 --分区后，使用全局索引的性能
3、技术核心
--创建新的分区表
--使用交换分区或者使用导入/导入数据
4、迁移测试语句
--测试表：SFC_BROOKNER_PARAM_LOG
--测试语句：通过生产系统，获取v$sql中的sql_text获取关于SFC_BROOKNER_PARAM_LOG的语句。
二、实施方法
1、迁移方法1——expdp/impdp在线迁移
（expdp/impdp等停机窗口短，最后一次导入数据进行停机）
（1）创建新的表，和原表结构一致（单独表空间） （2）创建表时用时间列做范围分区 （3）用expdp根据条件分多次导出要保留的数据 （4）用impdp分次导入，追加数据方式 （5）创建分区索引(本地分区索引或全局分区索引） （6）将最近的数据最后停业务导出，再导入新表 （7）更换表名，更新索引，更新统计信息 （8）测试新数据 （9）删除原表。
2、迁移方法2——expdp/impdp停机迁移
（1）导出全库expdp （2）创建新用户/新表空间 （3）导入数据2—除了sfc_brookner_param_log表的其他所有数据 （4）导入数据1—针对sfc_brookner_param_log表 --创建sfc_log表时用时间列做范围分区 --创建新的表，和原表结构一致（单独表空间） --根据筛选条件，使用impdp导入有效数据到新用户下/表空间下 --创建索引 --更新统计信息 （5）更新统计信息 （6）测试新数据 （7）删除原表。
3、迁移方法3—（交换分区方法）
（1）创建新表sfc_log_new（使用分区方法）
首先给时间列创建索引，用于后面查看筛选行数。
（2）筛选100万/1000万行数据，进行数据中转表的创建。
（3）创建中间表sfc_mid（以100万或1000万为中间表）
create table as select * from sfc_log where ****;
（4）使用上述中间表与上述的空分区进行交换
（5）持续交换其他数据到范围分区
（6）保留最后最新的数据，停库，进行范围分区切换
（7）测试
（8）备份并删除原表
4、迁移方法4：与方法3雷同
首先，总体来说要将表改造为分区表，这样便于以后的维护，在这个前提下的改造方法是：
（1）备份原表，新建一张同样结构的分区表；并创建一个包含现有数据的分区 （2）将现有表与分区表做交换 （3）在新分区表创建新的分区，后续的数据进来就到对应分区里了 （4）将分区表rename为业务表名 （5）过一段时间等以前历史数据使用不频繁时，将历史数据的分区做split，split成更细区间的分区 （6）最后在适当的时间将历史数据做分区drop或truncate
这个方案对业务影响小，缺点是跨度时间久。
5、其他思路
（1）对删除的数据分片，多线程执行（相当于并行）--分片就是把你要上 数据，分开进行删除。 （2）或者使用游标。
三、迁移方法1—expdp/impdp在线迁移
如果无需收缩之前的数据表空间，直接使用步骤2中的（五、创建分区表测试—分批次导入数据（这是核心内容））即可。见：https://www.topunix.com/post-10193.html#_-5
此方式，只对SFC_BROOKNER_PARAM_LOG表操作清理，清理完成后，无法进行dbf收缩
1、先给时间列做索引
（1）创建索引用的表空间
create tablespace tbsp_sfc_log_index datafile '/oracle/oradata2/sfc_log_index_01.dbf' size 10g autoextend on next 2g uniform size 16M ;
（2）并行索引使用新的表空间-共耗时520秒（单行方式需要800秒）。
create index sfc_brookner_param_log_index_time on sfc_brookner_param_log(datetime_created) nologging parallel(degree 4) tablespace tbsp_sfc_log_index ;
（3）创建完成，关闭索引的并行模式（oltp数据库下不要使用并行模式）
alter index sfc_brookner_param_log_index_time noparallel; 或： alter index sfc_brookner_param_log_index_time parallel(degree 1);
（4）收集表的统计信息（耗费1101秒）
begin dbms_stats.gather_table_stats(ownname => 'MESPRD', tabname => 'SFC_BROOKNER_PARAM_LOG'); end;
（5）查看表行数及索引（753753557行-40秒）
select count(*) from sfc_brookner_param_log where datetime_created<=to_date('2023/05/31 00:00:00','yyyy/mm/dd hh24:mi:ss');
2、创建表空间（用于存放分区表）
（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 next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; 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;
3、创建交换表
注：表结构和表SFC_BROOKNER_PARAM_LOG相同，使用分区方式。
（1）获取生产表方式
select dbms_metadata.get_ddl('TABLE','SFC_BROOKNER_PARAM_LOG'，'MESPRD') from dual;
（2）创建测试表，并进行范围分区
注：此处有主键，会创建一个主键索引，此索引是普通索引（全局非分区索引）。
create table sfc_brookner_param_log_range_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）导出要求
不导统计信息，不导索引：
（2）配置parfile参数文件
（根据服务器情况开或不开并行）
vi expdp_sfc_param_log_2020.par vi expdp_sfc_param_log_2021.par vi expdp_sfc_param_log_2022.par vi expdp_sfc_param_log_2023_part.par (注：此处导出的数据为离当前时刻比较近的时间，因为最新数据，需要停机导入，保证数据一致性）
（3）执行导出任务
expdp parfile=expdp_sfc_param_log_2020.par expdp parfile=expdp_sfc_param_log_2021.par expdp parfile=expdp_sfc_param_log_2022.par expdp parfile=expdp_sfc_param_log_2023_part.par --约1亿数据，只用4分钟完成导出，且没有开并行。
5、导入数据（未开并行）
（1）导入要求
全部导入即可。
（2）配置导入参数
vi impdp_sfc_param_log_2020.par vi impdp_sfc_param_log_2021.par vi impdp_sfc_param_log_2022.par vi impdp_sfc_param_log_2023_part.par
（2）导入数据
impdp parfile=impdp_sfc_param_log_2020.par impdp parfile=impdp_sfc_param_log_2021.par impdp parfile=impdp_sfc_param_log_2022.par impdp parfile=impdp_sfc_param_log_2023.par （导入1.1亿条数据，并行度为1，导入时间38分钟；并行度为12，导入时间？分钟）
6、停库并重启
（1）将数据库置为非归档模式
（导入数据会产生大量的redo和archivelog)
$lsnrctl stop shutdown immediate; startup mount; alter database noarchivelog; archive log list; alter databse open;
（2）导出最新数据
vi expdp_sfc_param_log_2023_end.par expdp parfile=expdp_sfc_param_log_2023_end.par
（3）导入最新数据
vi impdp_sfc_param_log_2023_end.par impdp parfile=expdp_sfc_param_log_2023_end.par
7、收集新表的统计信息
begin dbms_stats.gather_table_stats(ownname => 'MESPRD', tabname => 'SFC_BROOKNER_PARAM_LOG_RAGNE_P'); end;
8、获取当前索引的DDL
（1）查看表（SFC_BROOKNER_PARAM_LOG）有哪些索引
select * from user_indexes where table_name='SFC_BROOKNER_PARAM_LOG';
IDX_SFC_BROOKNER_PARAM_LOG SYS_C0073069 SFC_BROOKNER_PARAM_LOG_IX2
（2）获取创建索引的DDL语句
select dbms_metadata.get_ddl('INDEX','SFC_BROOKNER_PARAM_LOG_IX2','MESPRD') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0073069','MESPRD') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_SFC_BROOKNER_PARAM_LOG','MESPRD') from dual;
9、创建本地分区索引或全局分区索引
根据需要的列，创建分区索引。
（1）时间列-datectime_reated的索引必须要创建（本地分区索引）
create index sfc_broo_peralog_range_p_index on SFC_BROOKNER_PARAM_LOG_RANGE_p(datetime_created) local;
（2）其他组合索引（开发沟通索引重建问题，需提前沟通）
10、测试新数据（创建完了第九，十的任务，同步测试）
（1）原表删除前空间占用
（2）原表删除后空间占用
（3）查询脚本
11、原表删除，新表重命名为原表
（1）查出当前数据表在哪些数据文件中Q
select tablespace_name,file_id from dba_extents where segment_name='SFC_BROOKNER_PARAM_LOG' group by file_id,tablespace_name order by tablespace_name;
MESPRD_TBSP 2 MESPRD_TBSP 5 MESPRD_TBSP 8 MESPRD_TBSP 9 MESPRD_TBSP 10 MESPRD_TBSP 11 MESPRD_TBSP 13 MESPRD_TBSP 14 MESPRD_TBSP 15 MESPRD_TBSP 16 MESPRD_TBSP 17 MESPRD_TBSP 18 MESPRD_TBSP 19 MESPRD_TBSP 20 MESPRD_TBSP 21 MESPRD_TBSP 22 MESPRD_TBSP 23 MESPRD_TBSP 24 MESPRD_TBSP 25 MESPRD_TBSP 26 MESPRD_TBSP 27 MESPRD_TBSP 28 MESPRD_TBSP 29 MESPRD_TBSP 30 MESPRD_TBSP 31 MESPRD_TBSP 32 MESPRD_TBSP 33 MESPRD_TBSP 34 MESPRD_TBSP 35 MESPRD_TBSP 36 MESPRD_TBSP 37 MESPRD_TBSP 38 MESPRD_TBSP 39 MESPRD_TBSP 40 MESPRD_TBSP 41 MESPRD_TBSP 42 MESPRD_TBSP 43 MESPRD_TBSP 44 MESPRD_TBSP 45 MESPRD_TBSP 46 MESPRD_TBSP 47 MESPRD_TBSP 48 MESPRD_TBSP 49 MESPRD_TBSP 50 MESPRD_TBSP 51 MESPRD_TBSP 52 MESPRD_TBSP 53 MESPRD_TBSP 54 MESPRD_TBSP 55 MESPRD_TBSP 56 MESPRD_TBSP 57 MESPRD_TBSP 58 MESPRD_TBSP 59 MESPRD_TBSP 60 MESPRD_TBSP 61 MESPRD_TBSP 62 MESPRD_TBSP 63 MESPRD_TBSP 64 MESPRD_TBSP 65 MESPRD_TBSP 66
（2）查询相关索引
select * from user_indexes where table_name='SFC_BROOKNER_PARAM_LOG';
IDX_SFC_BROOKNER_PARAM_LOG SYS_C0073069 SFC_BROOKNER_PARAM_LOG_INDEX_TIME SFC_BROOKNER_PARAM_LOG_IX2
（3）删除原表及相关索引及约束等
drop table SFC_BROOKNER_PARAM_LOG cascade constraint;
（4）重命名新表名
alter table SFC_BROOKNER_PARAM_LOG_RANGE_P rename to SFC_BROOKNER_PARAM_LOG ;
12、收缩空间方案1（不适合）
（1）收缩表空间
清理上述大表后，因为水位线下降了，就可以收缩表空间了。
但是在收缩表空间文件时，出现报错：
ORA-03297: file contains used data beyond requested RESIZE value
（2）报错后，查询
Truncate table 或者 drop table 收缩数据文件,经常遇到ORA-03297: file contains used data beyond requested RESIZE value 查询dba_free_space 也有空闲空间。
经过查询MOS(Doc ID 1029252.6)得知
If you have a large extent in the middle of a datafile, and some object taking up room at the end of the datafile, you can use the query FINDEXT.SQL below to find this object. If you export this object, then drop it, you should then free up contiguous space at the end of your datafile so you will be able to resize it smaller.
Make sure you leave enough room in the datafile for importing the object back into the tablespace.
意思是说如果空闲的extent如果在文件的中间,此时无法进行resize ,必须把尾部的object drop 然后重建 再resize datafile。
（3）查看每个数据文件包含的表内容
其中一个数据文件file_id=56的，就包含了136个表，有60多个文件，每个文件中包含了各种表的信息，所以通过手动迁移表数据到其他表空间的方式不适合收缩表空间。
13、通过v$SQL获取生产库关于表SFC_BROOKNER_PARAM_LOG的信息
（1）查询关于SFC_BROOKNER_PARAM_LOG的语句
select sql_text,parse_calls,buffer_gets,disk_reads from v$sql where sql_text like '%SFC_BROOKNER_PARAM_LOG%' order by 3 desc; select sql_text,sql_id from DBA_HIST_SQLTEXT where dbms_lob.substr(sql_text,200) like '%SFC_BROOKNER_PARAM_LOG%'
（2）筛选出的语句（后期用于分析）
关于SFC_BROOKNER_PARAM_LOG的sql语句 xlsx文件：
