1. 迁移表空间(oracle 11g)
MOVE会锁表,locked mode=6,专用锁,其他操作无法进行
--1.1 move tablespace
alter table USER1.TABLETEST1 MOVE TABLESPACE TBS_USER;
--1.2 重建索引
SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE TBS_USER;' FROM dba_indexes
where OWNER = 'USER1' and TABLE_NAME = 'TABLETEST1';
alter index USER1.IDX_USER1_ID REBUILD TABLESPACE TBS_USER;
2. 在线迁移表空间(oracle 12.2)
oracle 12.2新特性
--1.1 move tablespace
alter table USER1.TABLETEST1 MOVE ONLINE TABLESPACE TBS_USER;
--1.2 重建索引
SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE TABLESPACE TBS_USER;' FROM dba_indexes
where OWNER = 'USER1' and TABLE_NAME = 'TABLETEST1';
alter index USER1.IDX_USER1_ID REBUILD ONLINE TABLESPACE TBS_USER;
3. 迁移后收集统计信息
--查询统计信息状态是否被锁
--oracle 11g进行MOVE TABLESPACE后,会导致统计信息被锁,无法进行统计信息收集
select owner, table_name, stattype_locked from dba_tab_statistics a
where table_name='TABLETEST1'
and a.stattype_locked in ('ALL','DATA','CACHE')
and owner = 'USER1';
--统计信息解锁
begin
dbms_stats.unlock_table_stats(ownname => 'USER1',
tabname => 'TABLETEST1');
end;
/
--收集统计信息
begin
dbms_stats.gather_table_stats(ownname => 'USER1',
tabname => 'TABLETEST1');
end;
/