作者：Digital Observer（施嘉伟） Oracle  ACE Pro: Database PostgreSQL  ACE Partner 11年数据库行业经验，现主要从事数据库服务工作 拥有Oracle  OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、 PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证 ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师 公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；墨天轮：Digital Observer；PGFans：施嘉伟。

一 说明

1.1 迁移说明

把表空间TS_HX_SB_IDX、IDX_SSD_HX中的全部索引迁移至表空间TS_SSD_IDX。

1.2 迁移流程

整体迁移流程:（因为索引量很大，需要分批进行迁移）

总结时间：4个晚上 总计花费业务停机时间：0

二 索引迁移详细操作流程

2.1 前期准备

统计各个用户在此两表空间的索引量大小，以便计划迁移。

SQL> select segment_type,sum(bytes)/1024/1024 m from dba_segments where owner='HX_SB' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') group by segment_type;
SEGMENT_TYPE                M
------------------ ----------
INDEX               494088.25
INDEX PARTITION    236043.688
SQL> select segment_type,sum(bytes)/1024/1024 m from dba_segments where owner='HX_ZS' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') group by segment_type;
SEGMENT_TYPE                M
------------------ ----------
INDEX                45650.75
INDEX PARTITION    99813.3125
SQL> select segment_type,sum(bytes)/1024/1024 m from dba_segments where owner='HX_RD' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') group by segment_type;
SEGMENT_TYPE                M
------------------ ----------
INDEX               8373.6875

。。。。。。。。

这些都没有分区： 单位(M) HX_YH 50 HX_RD 8373.6875 HX_JC 4.0625 HX_PZ 640.4375 HX_DJ 3732 HX_ZM 39

综上：计划先迁移HX_RD、 HX_YH、HX_RD、HX_JC、HX_PZ、HX_DJ、HX_ZM这些用户的索引，再迁移HX_ZS，最后分批迁移HX_SB索引。

2.2 撰写迁移脚本（无分区）

重建普通索引脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0
spool /home/oracle/idx/hx_rd.sql
select 'alter index '||a.owner||'.'||a.index_name||' rebuild tablespace TS_SSD_IDX online parallel 4;' from dba_indexes a where a.owner='HX_RD' and not exists(select * from dba_part_indexes b where a.owner=b.owner and a.index_name=b.index_name) and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');
spool off;

修改parallel脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0 
spool /home/oracle/idx/hx_rd_parallel.sql
select 'alter index '||a.owner||'.'||a.index_name||' noparallel;' from dba_indexes a where a.owner='HX_RD' and not exists(select * from dba_part_indexes b where a.owner=b.owner and a.index_name=b.index_name) and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');
spool off;

注意事项： 把hx_dr.sql和hx_rd_parallel.sql中无关信息删除。（比如select ‘alter’…… 最后的33 rows selected），hx_rd.sql文件中只保留alter index hx_rd.index_name rebuild TS_SSD_IDX online parallel 4; hx_rd_parallel.sql文件中只保留 alter index hx_rd.index_name noparallel;即可。

最后撰写hx_rd.sh脚本。

vi hx_rd.sh
[oracle@ljw sql]$ cat 2.sh
#!/bin/sh
sqlplus "/ as sysdba" <<EOF
host echo "*******************begin rebuild*****************",`date`
@idx/hx_rd.sql;
host echo "*******************end rebuild*******************",`date`
host echo "*******************begin noparalel***************",`date`
@hx_rd_parallel.sql;
host echo "*******************end noparalel*****************",`date`
EOF

2.3 撰写迁移脚本（有分区） 重建普通索引脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0
spool /home/oracle/idx/hx_zs.sql
select 'alter index '||a.owner||'.'||a.index_name||' rebuild tablespace TS_SSD_IDX online parallel 4;' from dba_indexes a where a.owner='HX_ZS' and not exists(select * from dba_part_indexes b where a.owner=b.owner and a.index_name=b.index_name) and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');
spool off;

重建分区索引脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0
spool /home/oracle/idx/hx_zs_part.sql
select 'alter index '||a.owner||'.'||a.index_name||' rebuild partition '||b.partition_name||' tablespace TS_SSD_IDX online parallel 4;' from dba_part_indexes a,dba_ind_partitions b where a.owner=b.index_owner and a.index_name=b.index_name and a.owner='HX_ZS' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');
spool off;

修改普通索引parallel脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0 
spool /home/oracle/idx/hx_zs_parallel.sql
select 'alter index '||a.owner||'.'||a.index_name||' noparallel;' from dba_indexes a where a.owner='HX_RD' and not exists(select * from dba_part_indexes b where a.owner=b.owner and a.index_name=b.index_name) and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');
spool off;

修改分区索引parallel脚本：

su – oracle
sqlplus / as sysdba
set lin 400
set pagesize 0 
spool /home/oracle/idx/hx_zs_part_parallel.sql
select 'alter index '||a.owner||'.'||a.index_name||' noparallel;' from dba_part_indexes a,dba_ind_partitions b where a.owner=b.index_owner and a.index_name=b.index_name and a.owner='HX_ZS' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') group by a.owner,a.index_name;
spool off;

注意事项和前面一样，删除。sql中无关信息。

最后撰写hx_rd.sh脚本。

vi hx_rd.sh
#!/bin/sh
sqlplus "/ as sysdba" <<EOF
host echo "*******************begin rebuild*****************",`date`
@idx/hx_zs.sql;
@hx_zs_part.sql;
host echo "*******************end rebuild*******************",`date`
host echo "*******************begin noparalel***************",`date`
@hx_zs_parallel.sql;
@hx_zs_part_parallel.sql;
host echo "*******************end noparalel*****************",`date`
EOF

2.4 执行脚本 cd 到相应目录下 nohup ./hx_rd.sh & 即可

2.5 检查索引迁移成功与否 检查源表空间中是否还存在索引：

select index_name,degree,tablespace_name from dba_indexes where owner='HX_RD' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX');

检查迁移到的表空间中是否有索引：

select index_name,degree,tablespace_name from dba_indexes where owner='HX_RD' and tablespace_name in ('TS_SSD_IDX');

常用语句：

如： 查询HX_SB用户下普通索引的大小：

select segment_name,segment_type,bytes/1024/1024 m from dba_segments where owner='HX_SB' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') and segment_type='INDEX' order by bytes;

查询HX_SB用户下分区索引的大小：

select segment_name,segment_type,partition_name,bytes/1024/1024 m from dba_segments where owner='HX_SB' and tablespace_name in('TS_HX_SB_IDX','IDX_SSD_HX') and segment_type='INDEX PARTITION' order by bytes;