oracle下表所属表空间的迁移

迁移目的：
将USER01用户下TABLENAME表，迁移到新的表空间DATA01。

--查询表是否有子分区
select * from dba_segments 
where OWNER = 'USER01' 
and SEGMENT_NAME  = 'TABLENAME'
--查询表的索引
select  OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME  from dba_indexes t
where TABLE_NAME='TABLENAME'
and OWNER='USER01'

--如果表未进行分区，则用如下命令移动表
alter table USER01.TABLENAME move tablespace DATA01;
--如果有进行分区，则用如下迁移表下子分区
ALTER TABLE USER01.TABLENAME MOVE SUBPARTITION partition01 TABLESPACE DATA01;
--批量生成迁移子分区语句
SELECT  'ALTER TABLE '||T.OWNER||'.'||T.SEGMENT_NAME||' MOVE SUBPARTITION '||T.PARTITION_NAME||' TABLESPACE DATA01;' 
FROM DBA_SEGMENTS T
WHERE T.SEGMENT_NAME  = 'TABLENAME'
AND T.OWNER = 'USER01' 

--重建表的索引
alter index USER01.INDEX1 rebuild tablespace DATA01;
--重建子分区索引
ALTER INDEX USER01.INDEX1 REBUILD PARTITION partition01 TABLESPACE DATA01;
--批量生成重建子分区索引语句
SELECT  'ALTER INDEX '||T.OWNER||'.'||T.SEGMENT_NAME||' REBUILD PARTITION '||T.PARTITION_NAME||' TABLESPACE DATA01;' 
FROM DBA_SEGMENTS T
WHERE T.SEGMENT_NAME  = 'TABLENAME'
AND T.OWNER = 'USER01'