1.收集表信息
2.给用户新表空间授权使用权限
alter user userxxxx quota unlimited on tablespace tablespacexxxx;
alter session enable parallel dml;
alter session enable parallel ddl;3、迁移表数据
alter table userxxxx.xxx enable row movement;
alter table userxxxx.xxx move tablespace tablespacexxxx parallel 8;
alter table userxxxx.xxx move tablespace tablespacexxxx lob (colxxxx) store as(tablesapce tablespacexxxx);
alter table userxxxx.xxx noparallel disable row movement;
///
alter table userxxxx.xxx2 enable row movement;
alter table userxxxx.xxx2 move tablespace tablespacexxxx parallel 8;
alter table userxxxx.xxx2 noparallel disable row movement;4、重建表索引
alter index userxxxx.indexxxx rebuild tablespace PSAPSR3_DB05 parallel 64 nologging;
alter index userxxxx.indexxxx noparallel logging;5、检查表空间使用率 表数据和索引状态 业务核对数据
select index_name,status,degree from user_indexes where table_name in('xxx');
select table_name,tablespace_name from user_tables where table_name in('xxx');
最后收集统计信息
alter session enable parallel dml;
alter session enable parallel ddl;
exec dbms_stats.gather_table_stats(OWNNAME=>'user1',TABNAME=>'tablexxxx',estimate_percent=>dbms_stats.auto_sample_size,DEGREE=>64,CASCADE=>true);
exec dbms_stats.gather_table_stats(OWNNAME=>'user2',TABNAME=>'tablexxxx2',estimate_percent=>dbms_stats.auto_sample_size,DEGREE=>64,CASCADE=>true);