1.查看表空间里面的对象

SELECT OWNER AS OWNER,
SEGMENT_NAME AS SEGMENT_NAME,
SEGMENT_TYPE AS SEGMENT_TYPE,
SUM (BYTES) / 1024 / 1024 AS SEGMENT_SIZE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'TBS'
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;

2.里面的普通表按照如下move到新表空间中

move后索引会应为rowid的改变而失效，因此需要重建

alter table owner.tablename move tablespace ntbs;
alter index owner.ix_tablename_id rebuild tablespace ntbs online;

3.里面如果是分区表，按如下move分区到新分区

SELECT    'ALTER TABLE '  
       || table\_owner  
       || '.'  
       || TABLE\_NAME  
       || ' MOVE PARTITION '  
       || PARTITION\_NAME  
       || ' TABLESPACE NTBS;'  
  FROM DBA\_TAB\_PARTITIONS  
 WHERE TABLE\_OWNER = 'OWNER' AND TABLE\_NAME = 'TABLE\_NAMES';

如果这个表是自动分区的，修改新增分区默认表空间

SELECT 'alter table '
|| owner
|| '.'
|| table_name
|| ' modify default attributes tablespace NTBLS;'
FROM DBA_TABLES
WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE_NAME';

4.里面如果是普通索引，按如下重建索引

alter index owner.tablename REBUILD TABLESPACE NTBS ONLINE;

如果online遇到如下错误

• 第 1 行出现错误:

• ORA-00604: 递归 SQL 级别 1 出现错误

• ORA-01450: 超出最大的关键字长度 (3215)

那么不加online即可（oracle规定一个数据块中至少存放一个索引节点，详细可参考[https://blog.csdn.net/yidian815/article/details/16336911](https://blog.csdn.net/yidian815/article/details/16336911)）

5.例如如果是分区索引，按如下重建索引

SELECT 'ALTER INDEX '
|| index_owner
|| '.'
|| index_name
|| ' REBUILD PARTITION '
|| PARTITION_NAME
|| ' TABLESPACE NTBS ONLINE;'
FROM DBA_ind_PARTITIONS
WHERE index_owner = 'index_owner' AND INDEX_NAME = 'index_name';

如果分区为自增分区，修改分区索引的默认表空间

SELECT 'ALTER INDEX '
|| owner
|| '.'
|| index_name
|| ' modify default attributes tablespace ntbs;'
FROM dba_indexes
WHERE OWNER = 'index_owner' AND INDEX_NAME = 'index_name';