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)
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';