1.修改分区表分区表空间
SELECT 'ALTER TABLE '
|| table_owner
|| '.'
|| TABLE_NAME
|| ' MOVE PARTITION '
|| PARTITION_NAME
|| ' TABLESPACE D_TBS_NEW;'
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = 'OWNER' AND TABLE_NAME IN ('TABLE_NAMES') ALTER TABLE OWNER.TABLENAME MOVE PARTITION PAT_P201906 TABLESPACE D_TBS_NEW;
2.修改分区表默认表空间
分区表的默认表空间即表定义里面的tablespace，不是分区定义里面的tablespace。如果表为自动分区，那么新分区会直接建到默认表空间里面
SELECT 'alter table '
|| owner
|| '.'
|| table_name
|| ' modify default attributes tablespace D_TBS_NEW;'
FROM DBA_TABLES
WHERE OWNER = 'OWNER' AND TABLE_NAME IN ('TABLE_NAME') alter table OWNER.TABLENAME modify default attributes tablespace D_TBS_NEW;
3.修改分表索引分区表空间
SELECT 'ALTER INDEX '
|| index_owner
|| '.'
|| index_name
|| ' REBUILD PARTITION '
|| PARTITION_NAME
|| ' TABLESPACE D_TBS_NEW;'
FROM DBA_ind_PARTITIONS
WHERE index_owner = 'OWNER' AND INDEX_NAME LIKE '%TABLENAME%' ALTER INDEX OWNER.INDEXNAME REBUILD PARTITION PAT_P201906 TABLESPACE D_TBS_NEW;
4.修改普通索引表空间
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| index_name
|| ' REBUILD TABLESPACE I_TBS_NEW;'
FROM dba_indexes
WHERE owner = 'OWNER' AND table_name IN ('TABLE_NAME') ALTER INDEX OWNER.INDEXNAME REBUILD TABLESPACE I_TBS_NEW; --这里面可能会包含分区索引，执行的时候会报错，忽略即可
5.修改分区索引默认表空间
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| index_name
|| ' modify default attributes tablespace I_TBS_NEW;'
FROM dba_indexes
WHERE OWNER = 'OWNER' AND table_name in ('TABLE_NAMES') alter index OWNER.INDEXNAME modify default attributes tablespace I_TBS_NEW; --这里面可能包含普通索引，执行会报错，忽略即可