【迁移】Oracle分区表及索引迁移表空间

近期计划使用XTTS方式迁移某库，在进行自包含检查时发现，该库有部分数据（分区表、索引）存放于SYSTEM表空间中，需要先将这部分数据移动到要迁移的表空间中。

一、环境说明

操作系统：CentOS 5.7
数据库版本：11.2.0.3

二、准备工作

1、表空间自包含检查

execute sys.dbms_tts.transport_set_check('USERS,XXXX', true);
select * from sys.transport_set_violations;

结果

ORA-39921: Default Partition (Table) Tablespace SYSTEM for LINSHI_XXXX_LISHI not contained in transportable set.
ORA-39906: Constraint FK_XXXXX_FOURLS between table LINSHI_XXXX_LISHI in tablespace SYSTEM and table LINSHI_XXXX_BIND in tablespace ORCLTBS.
ORA-39908: Index LINSHI_XXXX_BOX in tablespace SYSTEM enforces primary constraints  of table LINSHI_XXXX_BOX in tablespace ORCLTBS.
ORA-39911: Index XXXX_LISHI_KEY in tablespace ORCLTBS points to partition SYS_P5721 of table LINSHI_XXXX_LISHI in tablespace SYSTEM outside of transportable set.
ORA-39901: Partitioned table LINSHI_XXXX_LISHI is partially contained in the transportable set.
...

通过该步骤确定需要迁移的分区表、索引，及需要重建的索引

2、检查是否非系统默认用户数据存放在SYSTEM表空间

--确认用户情况
select username,user_id,account_status,created from dba_users order by user_id asc;

结果：user_id小于84的用户为系统默认用户

--检查是否非系统默认用户数据存放在SYSTEM及SYSAUX表空间中
col owner for a10
col segment_name for a50
col segment_type for a20
set linesize 150 pagesize 10000

select owner,segment_name, PARTITION_NAME,segment_type 
from dba_segments 
where tablespace_name in ('SYSTEM') 
and owner not in (select username from dba_users where user_id < 84);

结果：

OWNER      SEGMENT_NAME                                       PARTITION_NAME                                               SEGMENT_TYPE
---------- -------------------------------------------------- ------------------------------------------------------------ --------------------
用户名     XXXX1_LISHI                              SYS_P5041                                                    TABLE PARTITION
用户名     XXXX2_LISHI                              SYS_P10101                                                   TABLE PARTITION
用户名     XXXX3_LISHI                              SYS_P10103                                                   TABLE PARTITION
用户名     XXXX4_LISHI                              SYS_P10102                                                   TABLE PARTITION
用户名     XXXX_BOTTLE                                                                                              INDEX
用户名     XXXX_BOTTLELS                                                                                            INDEX
用户名     XXXX_BOX                                                                                                 INDEX
用户名     XXXX_BOXLS                                                                                               INDEX
用户名     XXXX_KEY                                                                                                 INDEX

显示5个索引、4个分区表存放在SYSTEM表空间中

3、查看分区表及索引DDL语句

set heading off　
set echo off
Set pages 999
set long 90000

spool get_TABLE_ddl.sql　
select dbms_metadata.get_ddl('TABLE','TABLE_NAME'[,'SCHEMA_NAME']) from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME'[,'SCHEMA_NAME']) from dual;
spool off;

4、检查分区表及索引大小

需要确定要迁移分区表的大小及SYSTEM表空间中分区个数,确保目标表空间中有足够容量

--分区表
SELECT sum(BYTES)/1024/1024/1024 FROM dba_segments t WHERE t.segment_name ='表名'; 

--索引
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 from dba_segments t 
WHERE t.segment_type ='INDEX' and t.segment_name in ('索引名1','索引名2')
GROUP BY t.owner,t.segment_name 
ORDER BY SUM(bytes) desc;

5、查看UNUSABLE状态索引

select index_name,table_name,TABLESPACE_NAME,STATUS from dba_indexes where status ='UNUSABLE';

6、查看dba_objects表中有哪些失效对象

select owner,object_name,object_type,status from dba_objects where status='INVALID';

三、移动分区表操作

选择业务窗口期进行，操作前查看目标表是否有DML操作，避免锁表，有条件可选择停机操作

1、修改分区表默认表空间

--批量生成语句
select 'alter table '|| owner||'.'||table_name ||' modify default attributes tablespace 表空间名;' from dba_tables where table_name in ('分区表1','分区表2') and owner='用户名';

修改后新生成分区会到正确表空间

2、修改分区表分区的表空间

--查看分区所在表空间
select table_name,partition_name,tablespace_name from dba_tab_partitions where table_name in ('分区表1','分区表2');

--批量生成语句
select 'alter table '||owner||'.'||segment_name||' move partition '||PARTITION_NAME||' tablespace 表空间名;' from dba_segments where tablespace_name in ('SYSTEM') and owner not in (select username from dba_users where user_id < 84) and SEGMENT_TYPE='TABLE PARTITION';

move partition需要一定时间

3、重建索引

alter index 用户名.索引名 REBUILD tablespace 表空间名 online parallel 4; 
alter index 用户名.索引名 noparallel; 

重建索引需要一定时间，可通过下面语句查看重建进度

col opname format a32
col target format a32
col perwork format a12
set linesize 1200

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
select sid,opname,target,sofar,totalwork,trunc(sofar/totalwork*100,2)||'%' as perwork from v$session_longops where sofar!=totalwork and sid=&sid;

参考：https://www.cnblogs.com/kerrycode/p/5673224.html

4、对比失效对象并重建

select owner,object_name,object_type,status from dba_objects where status='INVALID';

四、移动索引操作

1、查询索引所属表空间

select owner,index_name,tablespace_name from dba_indexes where index_name in ('索引1','索引2'）；

2、生成批量修改语句

alter index 用户名.索引名 REBUILD tablespace 表空间名 online parallel 4;
alter index 用户名.索引名 noparallel;

select 'alter index '||owner||'.'||segment_name||' rebuild tablespace ORCLTBS online parallel 4;' from dba_segments where tablespace_name ='SYSTEM' and owner not in (select username from dba_users where user_id < 84) and SEGMENT_TYPE='INDEX';
select 'alter index '||owner||'.'||segment_name||' noparallel;' from dba_segments where tablespace_name = 'SYSTEM' and owner not in (select username from dba_users where user_id < 84) and SEGMENT_TYPE='INDEX';

3、确定索引状态，索引段所在的表空间

select owner,table_name,index_name,status from dba_indexes where index_name in (索引1','索引2');
select owner,segment_name,segment_type,tablespace_name from dba_segments where segment_type='INDEX' and segment_name in (索引1','索引2') and tablespace_name='表空间名';

五、注意事项

1、执行分区移动的时候产生大量归档，注意空间；
2、批量执行alter 语句放到.sql文件中 @1.sql 执行；
3、索引没有重建完时，对表操作导致系统负载很高，建议移动一张表接着重建该表索引；
4、除索引重建外，可能还会有view及外键失效，同样需要重建；